Excel 2010 :: Delete Unwanted Character In VBA

May 15, 2012

I'm using excel 2010. I'm working with columns of values where most of the values are numbers - which is fine, and there are some numbers that have a "p" at the start of them.

e.g. Column has 49, 52.2, p56.7, 34

OK, I want to preserve the 56.7 but I want to delete the "p". I'm thinking I've got a mental block as to how to delete the unwanted "p"s but I can't think of how to do it at the moment! My code goes as follows:

If Left(Cells(1,1),1) = "p" then
'delete the "p" and leave the remaining number in tact
End if

View 4 Replies


ADVERTISEMENT

Excel 2010 :: How To Hide Unwanted Values Created By Formulas

Apr 16, 2014

I am trying to create an inventory list that automatically updates the supply quantity when items are taken away from inventory. I have created entry cells where the quantity of items taken out of inventory can be entered (example, cell F2 of the attachment), and the new overall inventory count is adjusted accordingly for each stock of items (example, cell B3). These automated adjustments are repeated for each subsequent row for every time items have been taken from inventory and recorded.

I managed to get it to do what I originally intended with exception of the fact that it still present values on rows where there were no activity - or quantity of items taken from out of inventory (row #6 and beyond of the attachment). Is there a way of "hiding" these values without removing the formula, or possibly set conditional formatting where no values are displayed if no changes in inventory has been entered?

Example.xlsx

Using Excel 2010

View 4 Replies View Related

Excel - Delete Unwanted Rows On Closing File

Dec 30, 2011

I want to delete some unwanted rows on closing my excel file. the blank rows are between set of datas. so it has to check all the blank rows, delete it until the last one. example

data
blank rows
data
blank rows
data
blank rows
data

View 4 Replies View Related

Excel 2010 :: Macro With Autofilter With Array To Remove Unwanted Criteria With Wildcard

Apr 28, 2014

I'm having a hard time making this maro work in Excel 2010.

I need it to filter out the items "AR", "BATCH", and the line of "Total:*" where the * is a total amount of any given number dependant on the day.

Below is the coding I have that Excel is not liking.

Sub FilterAccurateRawData()
'
' FilterAccurateRawData Macro
'
'
Rows("1:1").Select
Selection.AutoFilter
ActiveSheet.Range("$A$1:$AA$45415").AutoFilter Field:=1, Criteria1:=Array("<>AR", "<>BATCH", "<>Total:*")
Operator:=xlFilterValues
Sheets("Instructions").Select
Range("A9").Select
End Sub

View 3 Replies View Related

Excel 2010 :: Filter To Select Records - Removing Unwanted Blank Cells

Jul 25, 2013

I have a spreadsheet which is used by users unfamiliar with Excel. They are using the filter to select records, however when this is used some records appear which have no entry in the cells of that column. Can I overcome this? There is no data in the blank cells, other than a data validation drop down.

View 1 Replies View Related

Unwanted Character, Can Not Search And Replace

Dec 18, 2007

I have a sheet that I often have to edit and import into SQL database.

I have no control over the format of the sheet given to me and it seems that something has changed causing me the following problem.

When I try to import the Data it’s showing ten times as many items than are actually there.

The problem I think is in a text column. I’ve tested this by using an old column from a previous sheet and pasting this into the new sheet and I can import it ok.

What I seem to have is a rectangular box character but I’m not sure what it is.
I can copy and past it into Notepad as it is, but pasted into Word it seems to be a carriage Return.

So whether it’s an actual charter or not I’m not sure.

Are there any tools out there so I can hover over characters in Excel to see what it is! So at least I can be sure what I’m trying to Search and Replace?

View 9 Replies View Related

Excel 2010 :: Nth Character In A String - Parsing Shortcut?

Feb 1, 2013

I have an Excel sheet with lots of shortcuts in the first column. It's the entire shortcut, including the .lnk extension.

What I need to do is to be able to parse out the string to make the shortcut human readable when I create the hyperlink. I'm saving the output as HTML for a user to open these shortcuts. It's on an internal LAN & secure. Using various versions of Excel (2010, 2007 & 2003, predominately the first). Trying to make an ugly, 140+ character shortcut into a more readable form for web navigation purposes.

Up to a point, the shortcuts are similar:
a123meworkfolder1folder2folder3shortcut1.lnk
a123meworkfolder1folder4folder5shortcut1.lnk
a123meworkfolder1folder6folder7shortcut1.lnk
a123meworkfolder1folder8folder9shortcut1.lnk

but, later on, they vary from that point out.

a123meworkfolder1folder10folder10Afolder11shortcut1.lnk
a123meworkfolder1folder_12folder12Afolder12Bshortcut1.lnk

The strings are the same up to the 7th occurrence of the "" character, but after that, the foldernames start to change & the shortcut may be down a level (an extra folder) or more. Also, the lengths of the strings change as well, so I can only do positional parsing up to a point.

View 4 Replies View Related

Excel 2010 :: Using Find Function On A Range And Search For (,) Character

Jan 22, 2013

I want to use the find function on a range and search for a "," character. If I get a "," I want to return a True otherwise false.

I tried just recording a macro using the find function but no code shows up.

how to use the find feature in a macro, can this be done?

excel 2010

View 4 Replies View Related

Excel 2010 :: Increase Character Limit In Text Box That Is Based On Formula?

Mar 7, 2012

I have a text box that is set to "=B1". B1 is a cell from an Access table import with memo format. The text memo is long, probably on average between 1000-2000 characters. I have set up the text box to automatically resize for the text, but the text stops at what I assume is 255 characters, even though cell B1 displays the entire memo.

How can I set it up so that this text box (or any other similar shape) displays the text and retains its resize/wrap property?

This is done in Excel 2010.

View 1 Replies View Related

Excel 2007 :: Find And Delete Single Character?

Jan 23, 2014

Following the tips doesn't allow to remove a character.

I exported email addresses from Outlook and they have the following character ' in front of the email address.

Using Excel 2007.. it says :"check if your search formatting and criteria are defined correctly. If you are sure that matching data exists in this workbook, it may be on a protected worksheet. Excel cannot replace data on a protected worksheet"

So I made a new file, and copied and pasted the cells into the new spreadsheet.. same message.

View 4 Replies View Related

Delete Unwanted Rows

Jul 11, 2008

1. In excel Sheet how to delete the unwanted rows in a proper way
In the following Data :

Day Date A/c Time Code Description
Tue 08/07/10 1708 02:54 E301000 > AC Loss [ri 0: Residence]
Tue 08/07/10 1708 02:55 R130006 > Reset [ri 0: Residence]
Tue 08/07/10 1708 02:56 R301000 > AC Restoral [ri 0: Residence]
Tue 08/07/10 1708 03:00 E301000 > AC Loss [ri 0: Residence]
Tue 08/07/10 1708 03:40 R301000 > AC Restoral [ri 0: Residence]
Tue 08/07/10 1708 04:08 E301000 > AC Loss [ri 0: Residence]

Tue 08/07/10 2410 04:23 E301000 > AC Loss [ri 0: Residence]
Tue 08/07/10 2410 04:47 R130006 > Reset [ri 0: Residence]
Tue 08/07/10 2410 05:56 R301000 > AC Restoral [ri 0: Residence]
Tue 08/07/10 2410 06:21 E301000 > AC Loss [ri 0: Residence]
Tue 08/07/10 2410 06:30 R130006 > Reset [ri 0: Residence]..............

View 9 Replies View Related

Delete Or Remove The Unwanted Pages

Feb 22, 2005

I have a 'document' which shows 294 pages, I have only 10 actual pages. How
can I delete or remove the unwanted pages?

View 9 Replies View Related

Delete Unwanted Row And Combine Different Cells

Apr 20, 2012

ItemNew or UsedQuantityDescriptionScrap ReasonActionitem1new1000item1 description1item1 reason1item1 action1item2new1200item2 description1item2 reason1item2 action10000item2 reason2item2 action20000item2 reason3item2 action3item3used3500item3 description1item3 reason1item3 action1000item3 description2item3 reason2item3 action2

The above was imported to Excel from another software and somehow those unwanted rows with "0" were created.

Would like to delete those unwanted row and combine the reason on those rows into their respective rows above.

Please note: this is just a small part of the file. The file is big and row with "0" doesnt occur in a prodictable patten.

View 9 Replies View Related

Filter Then Delete Unwanted Rows

Oct 8, 2007

I am using the following code to filter for data I do not want and then to delete those rows and show remaining data. It works fine except when the filter comes up empty and there is no unwanted data to delete.how to improve this code to accomodate this situation?

Selection.AutoFilter Field:=4, Criteria1:=">" & dweekend, Operator:=xlAnd
Range("A2").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.EntireRow.Delete
ActiveSheet.ShowAllData
Thank you for reading

View 9 Replies View Related

Delete Unwanted Characters Within Certain Criterias

Jun 20, 2007

I have a spreadsheet that i manually edit each and everyday e.g.

A B C
EABGL/UD NDT254892
MRMR/RUS/ELQNS259762
LSL/UW/B LQNS267259
WWEX/UQ bbr263666
LWL/KL/B 270407
MYTCJ/UB NDT271774
LNL/SB/UB HLC - 271955
SMMQD/WT HLC - 269516
EACO/TN/UGBBR257827
NILVA/UC EUi273645

For everything that doesnt equal EM, LN, LW and TH in column A, everything should be deleted in column B.

For the remaining EM, LN, LW and TH, i would then like it to delete / (forward slash and all characters after this) so that this would make my life easier.

View 4 Replies View Related

AutoFilter VBA - Filter And Delete Unwanted Rows

Oct 25, 2012

I'm trying to filter and delete unwanted rows as I need row where dates is within a specified range.

How can I do this using AutoFilter? or are there any other alternatives?

View 2 Replies View Related

Autofilter To Delete Chunks On Unwanted Data

Feb 13, 2009

I am using an autofilter to delete chunks on unwanted data, by using Range(Selection, Selection.End(xlUp)).Select however it picks up the header row. Is there anyway I can either get the selection of data to select one less line, or a way to get it to leave the header line?

View 2 Replies View Related

Delete Unwanted Rows Based On Value In Cells?

Sep 28, 2011

I am aware this is probably very simple, but I haven't been able to find a usable macro yet. I would like the data in the workbook that is not "Tuesday" (column L) and is not "3" or "4" (column I) to be deleted. All rows that have "Tuesday" and either "3" or "4" should not be deleted. Each row that is not deleted must have Tuesday in column L. All other cells are blank intentionally. The workbook will remain the same titled WKBK1 (no need to put the output data in a separate workbook nor separate spreadsheet).

Do you know what the macro would be so I don't have to copy and paste a formula over a large number of cells?

WKBK1
A B C D E F G H I J K L 2
3

[Code]....

View 8 Replies View Related

Excel 2010 :: Cannot Delete A Range Name

Nov 28, 2013

Workbook contains a number of named ranges where the name is no longer used. I wish to remove the name assigned but not the actual range of referenced cells. When using Name Manager to try and delete the name the Delete button is greyed out and not available. Workbook is being heavily modified from its original form used by another. Is this possibly from a protected area from prior user? However, I have not had a problem creating new worksheets, cell ranges, etc. Also, even for new ranges I create I am not able to delete the name as the Delete button is greyed. What is going on and how can I delete just the unused range names? Version is Excel 2010.

View 3 Replies View Related

Excel 2010 :: VBA To Delete Rows?

Aug 5, 2013

I have some code that worked perfectly in Excel 2007 but crashes Excel 2010.

The part of the code that appears to be the problem is this:

Code:
For i = LR To 2 Step -1
If Cells(i, 15) = "Delete" Then Rows(i & ":" & i).EntireRow.Delete
Next i

For info., both screen updating and calculation are already both set to manual.

View 9 Replies View Related

Excel 2010 :: Unable To Delete Rows

May 30, 2014

I'm on mac Excel 2010 and i'm trying to delete rows..but for some reason they don't go! My workbook is a basic one, just filled with a bunch of formulas, i can't understand why it wont let me delete it.

When i do try to delete it, nothing comes happens and when i try to delete a large amount it comes up with 'not enough memory, continue without undo deleting rows' but my laptop has 8GB and my other laptop has 16GB so i can't see why a 16GB laptop wouldn't be able to delete it.

View 1 Replies View Related

Excel 2010 :: VBA - Delete Or Copy Row And Picture

Jan 23, 2012

I have a not too big catalog in Excel format (2010). Each row has a picture. Now, I need to generate sales report. Some items do not sell. For report purpose, I want to delete those rows along with the pictures. Possible to do so? I did an Internet search and found that drawing objects are not bonded to a row or a cell. Seems to me I cannot selectively delete picture in a particular row. True?

View 1 Replies View Related

Excel 2010 :: Weekly Import - How To Delete The Last Row

Feb 14, 2012

For a weekly import into Excel, I need to delete the last row each time. I'm using Excel 2010. My first thought is to start with the first cell, A1, and use .End(xlDown) to find the last row. Do I have to assign a variable to hold the row value then delete it? Or can it all be done in one statement without a variable?

View 4 Replies View Related

Excel 2010 :: Delete Empty Columns By VBA?

Aug 1, 2013

i am trying to come up with VBA code, sheet is attached. i have some columns that have headers but rows are empty . so the VBA should delete all the se columns entirely and leave those that has headers and have data in rows.

Excel 2010ABCDEFGHIJKLMNOPQRSTUVW1AccountUnitFund CodeDepartment
ActivityAnalysisTypecodedeskitemBegin DateQuantityUnit of MeasureAmount
CurrencyJob CodeEntry EventParent Budget Entry TypeOptionsLine CodeFunding
SourceFacilities and AdministrationCost Sharing2

[Code] ........

View 1 Replies View Related

Excel 2010 :: Delete Row If Cell Contains Date Or Any Value

Jan 6, 2014

I came across this excellent code here on this site but it does the opposite of what I need. This code deletes empty or blank rows if the cell in column I is blank. I actually need to delete the row if the cell in column I has a date or any data. Using Excel 2010.

Sub deleteBlankRows()
On Error Resume Next
Columns("I").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub

View 5 Replies View Related

Excel 2010 :: If Cell Contains Red Text In Column Then Delete Row?

Mar 10, 2014

I'm using Excel 2010). I have column I, which is the sum of columns A and B (first and last names of patrons), and I have used conditional formatting to identify all duplicates in column I with red text. I would now like a macro to find red text in column I and then delete the corresponding rows.

I've been looking around on the web for a while and have cobbled together this code stolen from other people's macros:

[Code] ....

When I run it, I get the following error:

Run-time error '1004':Application-defined or object-defined error

Excel doc is called "stupid mailing list.xlsm", sheet I want to run the macro on is called "voters".

View 1 Replies View Related

Excel 2010 :: How To Delete Rows Based On Cell Value

Sep 23, 2011

I have a massive spreadsheet (ca.110 000 rows [excel 2010). I need to cut this monster by deleting all the cells that do not have a certain cell value within them.

Say that in my column A there are different entries (text type). I would like to delete the ones that do not match the following criteria:

"mytextascriteria*", where * somehow stands for the rest of the string.

Strictly speaking I can only determine beggining of the string I want to perserve but the values at the end of that string are changing.

I found the thread about deleting entire row based on a cell value here:
Delete entire row based on one cell?

but I couldn't quite work out how to apply it in my scenario....

Otherwise is completely pointless to do it manualy with these amout of records.

View 5 Replies View Related

Excel 2010 :: Delete Row With Duplicate Data In 2 Columns?

Apr 21, 2014

Basically, I have a sheet and I would like to delete the entire row if the data in column G is the same as that in column H. The data is text if that matter. I've tried to figure out the VBA code for it, but my knowledge is severely limited. The spreadsheet is excel 2010.

View 3 Replies View Related

Reset Vertical Scroll And / Or Delete Unwanted Blank Rows At The Bottom?

Mar 18, 2014

My spreadsheet has 600rows and the vertical scroll bar ends at row 459424. I have attempted to delete all of the extra rows by holding SHIFT and CTRL, striking the down arrow then right-clicking on the left margin of highlighted row numbers and choosing delete.

But the unwanted rows are still there and my vertical scroll bar is not correlated to the 600 rows.

View 1 Replies View Related

Excel 2010 :: Find String And Delete Column On All Worksheets

Jul 9, 2014

Why my code is not working. I am working with Excel 2010. It will only delete the column on the active spreadsheet and not go to next worksheet. Not all worksheets contain the word "Broker".

[Code] ......

Not sure if its something to do with Activecell, try After:=ws.cells(1,1) or passing it in as the active cell stays the same i think.

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved