Free Microsoft Excel 2013 Quick Reference

Delete Rows With Specific Characters In a Specific Column

Currently I am using the Find-function of Aaron, but I would like to have something that works more efficiently.

What I currently do is (assuming all possible values for Column J are A - F):


	VB:
	
).EntireRow.Delete 
Find_Range("B", Columns("J"), MatchCase:=True).EntireRow.Delete 
Find_Range("C", Columns("J"), MatchCase:=True).EntireRow.Delete 
Find_Range("D", Columns("J"), MatchCase:=True).EntireRow.Delete 
Find_Range("E", Columns("J"), MatchCase:=True).EntireRow.Delete 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
although I just want some code that says: delete all rows except those that have "F" as content in Column J.

Thanks in advance for your help! Auto Merged Post;

And I already tried something like:


	VB:
	
Range("1:65536").Select 
For Each cl In Range("J:J") 
    If cl.Text = "A" Or cl.Text = "B" Or cl.Text = "C" Or cl.Text = "D" Or cl.Text = "E" Then 
        Rows(cl.Row).Delete 
    End If 
Next 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
but it also takes much to long. The major problem I think, is that the number of records is variable so I search the entire worksheet...


I found the following code on online:

"Delete rows with specific value in Column A and on same row specific value in column B "

   Sub Delete_rows_based_on_ColA_ColB()
      Application.ScreenUpdating = False
      Application.Calculation = xlCalculationManual
      Dim cell As Range, rng As Range, i As Long
      Set rng = Columns("A").SpecialCells(xlConstants, xlTextValues)
      For i = rng.Count To 1 Step -1
         If LCase(rng(i).Value) = "standard" _
              And LCase(rng(i).Offset(0, 1).Value) = "card" _
                Then rng(i).EntireRow.Delete
      Next i
      Application.Calculation = xlCalculationAutomatic
      Application.ScreenUpdating = True
    End Sub

I need help recoding it (if possible) so that it looks at all rows and if Column F, G, & H are <=0 then the row gets deleted, but all three columns (F, G, & H) MUST be <=0

As a side request, I cant figure out in the original code where you would enter the "specific value" to look for in A & B.

I have been trying to transpose a piece of code that was put on this forum back in 2005 into my own Workbook but have been unsuccessful. The original piece of code filtered a column for unique entries and then filtered the main sheet for each unique entry and cut and paste each row that the unique value occurred in and in a new worksheet. A new worksheet would be created for each unique filter.
So what I am trying to do is look up a list of values that i have in a list on the worksheet. Then for each entry in my list, create a new page and cut and pasted the filtered main worksheet into that page.
Finally return to the main worksheet and then remove all filters.

I have attached the original file that was submitted so you can look at how it works and I have posted a sample my worksheet (with some things disabled to reduce file size but hopefully you will get the layout. However I don't currently have that sample on this computer. It is posted on another thread under the title "Selecting rows with specific values in a column".
Thanks for any help or thoughts.

Cheers

Hi there all. I'm new here, and you've got some very useful information here. I hope that you can help with what appears to be a simple issue. I'm attempting to write a macro that will select all the rows which contain a value in a specific column. Say for example, all the instances of "test" in column C, I need to select the entire row with that value in the column.

I should also mention that by using an autofilter, only specific row numbers are selected. The source spreadsheet is dynamic and will not always display the same value in the same row, and thus I need something a little more adaptable.

Any suggestions for the macro?

Hi there,

I am trying, with no luck, to create a VBA macro in Excel that will
delete rows with data duplicated in 2 columns. Column A has ID
numbers and Column B has Dates. I need to delete rows that have
duplicate ID No. AND Date and leave the other rows on the worksheet.

I have tried Chip Pearson's code which works well however it doesn't
allow for the dates in column B so it considered the latest date to be
the record to leave and deletes the rest.

Sub DeleteTheOldies()
Dim RowNdx As Long
For RowNdx = Range("A1").End(xlDown).Row To 2 Step -1
If Cells(RowNdx, "H").Value = Cells(RowNdx - 1, "H").Value Then
If Cells(RowNdx, "I").Value

I want to delete any/all rows from a worksheet (named UK) which have the word "VAN" in column P.

I have tried using a 'For Each... Next statement' in a macro, but always get a debug box "Run Time Error '91' Object variable or With block variable not set". What am I doing wrong? my code follows


	VB:
	
Sheets("UK").Select 
Dim cell As Range 
For Each cell In Worksheets("UK").Range("P10:P200").Cells 
    Cells.Find(What:="VAN", _ 
    After:=ActiveCell, _ 
    LookIn:=xlFormulas, _ 
    LookAt:=xlPart, _ 
    SearchOrder:=xlByColumns, _ 
    SearchDirection:=xlNext, _ 
    MatchCase:=False, _ 
    SearchFormat:=False).Activate 
    Selection.EntireRow.Delete 
Next 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


I am copying raw data into a calculation sheet. I need to pre-process the data to remove all lines but the first that have the same value in one of the columns to pare down the size of the worksheet. Specifically, for the data example below I would like to have a macro that would delete all the rows with the value 0.8 below the first 0.8 in the last column and the last row in this set where there are two consecutive 0.84s. It is probably easy, but I only know how to record macros and this involves an if function, I think, that I don't know how to include in the macro. Any help would be greatly appreciated.

USGS 11070500.00 10/16/2005 0:00 2.82 0.84
USGS 11070500.00 10/16/2005 0:15 2.81 0.8
USGS 11070500.00 10/16/2005 0:30 2.81 0.8
USGS 11070500.00 10/16/2005 0:45 2.81 0.8
USGS 11070500.00 10/16/2005 1:00 2.81 0.8
USGS 11070500.00 10/16/2005 1:15 2.81 0.8
USGS 11070500.00 10/16/2005 1:30 2.81 0.8
USGS 11070500.00 10/16/2005 1:45 2.81 0.8
USGS 11070500.00 10/16/2005 2:00 2.81 0.8
USGS 11070500.00 10/16/2005 2:15 2.81 0.8
USGS 11070500.00 10/16/2005 2:30 2.81 0.8
USGS 11070500.00 10/16/2005 2:45 2.81 0.8
USGS 11070500.00 10/16/2005 3:00 2.81 0.8
USGS 11070500.00 10/16/2005 3:15 2.82 0.84
USGS 11070500.00 10/16/2005 3:30 2.82 0.84

Dear Experts ,

I have a spreadsheet with a million rows ,

Now the thing is if there are rows with identical data in column A ,B ,C,D,F then i want to get such rows deleted ,If there are 2 such rows ,then i want 1 row deleted ,
'If there 3 such rows ,then i want 2 deleted ,

If 4 rows ,i want 3 deleted ,

Basically i just want to have all rows with unique data in A,B,C,D,F,(Please dont check E ,if at all ,it cn just help that while selecting to delete rows ,the one with lesser data in E can be deleted first ,but other than that "E"is not useful to my study ,
I attach a excel file with input sheet ,you will see that rows 1 and 2 are identical and 2 has more characters in cell E ,so i delete 2 and keep 1 ,row 3 is a unique enry ,so i keep that as welll ,

This process for million rows or as long row data is available is what i need ,

Regards ,

Amlan Dutta

Hi,

I've got a large spreadsheet that I'd like to extract all the rows with the
words "PROVISIONAL QUANTITY" or "PROVISIONAL SUM" within the text in a
particular column.
ie - Some cells in Column C have the words "PROVISIONAL QUANTITY" or
"PROVISIONAL SUM" in the text somewhere. I need the data from the entire
row if col C has these words in the cell text.

I suspect this could be done by either 1) deleting all rows that dont
contain these words, or 2) selecting all the rows with the words and copying
into a blank worksheet.
Is there an easy way to achieve this or is a macro required. If macro
required, any help on the below macro would be great.

Sub ProvSumCopy()
Dim rng As Range
Dim cell As Range
Dim start_str As Integer

Set rng = Selection
For Each cell In rng
start_str = InStr(cell.Value, "PROVISIONAL SUM")
If start_str Then
ActiveRow.Select
End If
start_str = InStr(cell.Value, "PROVISIONAL QUANTITY")
If start_str Then
ActiveRow.Select
End If

Next

End Sub

Thanks
Troy

I have been struggling with a macro for my excel worksheet. I need a macro that will delete rows based on criteria in two columns. Specifically....

EXAMPLE (note row 1 is column headings)

ITEM CODE = COLUMN A
DESC = B
LOCATION = C
STATUS = D

I need to delete all rows in which the value of column C = "ASSEM"
I ALSO need to delete all rows in which the value of column D = "D" or "M"

I would really appreciate any help, thx.

Hi,

I have a huge spreadsheet that needs to have all rows with empty cells in
both columns B and C deleted. I was going through it line by line, but there
has got to be a better way. My knowledge of Excel and all of its features is
very limited. Any help would be appreciated.

Thank You,
Richard

Yesterday's comments about color recognition have solved a problem, but it looks like i have one more hurdle:color recognition of one specific character in a cell. Anyone familiar with an application like this?

I'm copying a large amout of data from an old MSDOS database into Excel, where I'm sorting data using coordinate formulas for each cell space.
ie. =LEFT(Input!A3,1) ; =MID(Input!$A3,2,1) etc
So, Excel extracts the desired part of the MSDOS screen/row, and leaves out what I don't need in my report.
In this way I've been able to isolate regularly occuring parts of the strings. like only the 9 digit sequence below- it always begins at the 7th space and goes to the 15th in the input cell.
Yesterday I learned how Excel could recognize the color of the text of a string (all text in the cell is the same color) in one cell.
Is there a way to get Excel to recognize if one character of a string in a cell is a certain color?
My copied data looks something like this
xxx xxxxxxxxx xx xxx xxxxxxxxx xx

The strings of data (actually rows from MSDOS or COBAL screens, copied in all together, a screen at a time so that each COBAL row fits completely into one EXCEL cell) have groups of characters marked by different text colors that I need to distribute to different cells in my report - actually into different sets of color-coded reports

So, is there a way to get Excel to recognize color of one specific character of a cell containing a string of characters and spaces??If so, I would think I could use something like the above formula in conjunction w/ =ColorIndexOfCell(A1,TRUE,TRUE) and =IF(B1=3,"It's Red","It's not red")

or

do I need to copy the MSDOS screen in a way that every character will have it's own EXCEL cell. (so that in string xxx xxxxxxxxx xxx xxxxxxxxx, each character and space gets it's own cell, rather than whole row to a cell)

and finally

[I]If the second copying method is a better method for writing formulas for this application, does anyone know how to do it???

Thanks!!!

Hello.

I am new to Excel macro programming in visual basic, and have a macro that I would like to use to make my work a bit easier.

I need to create a macro to search out the following strings of characters in a column:

"MIL-STD"
"J-STD"
"WS"
"0D"
"DWG"

When it finds any of these strings in the column, it would delete the associated row.

Any help on this would be greatly appreciated.

Thank you in advance!

Hi,

I need to filter data in a query based on the value of a specific character in a field.

My basic query looks like this:


	VB:
	
SELECT audit.ape, audit.fy2008, audit.fy2009, audit.fy2010, audit.fy2011, audit.fy2012, audit.fy2013 
FROM audit 
WHERE audit.ape ...??? 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I want to select data based on the fourth character of the 'ape' field. The 'ape' field has nine characters. I don't know how to get 'WHERE' to look at the fourth character in the 'ape' field and then filter if the fourth character is a '7'.

I would appreciate any help.

Thanks,
Craig

As the topic states, I am looking to change a specific character in a cell using VBA code. The twist is how to change the character one at a time.
For example: Changing 3's to 8's one at a time for a cell, where I would want to return something like change3once = 28253.213, change3twice = 28258.213, etc

I have been searching online, but had been unsuccessful in finding a solution, and was hoping someone on here may have a good solution

Hello, I was wondering is there a way to remove the first 2 characters in a column efficiently ?

I can do it manually but it would take forever due to the large database on the excel sheet.

Thanks!

Hi guys,

Hope this question isn't redundant; I tried to search for a similar one...

Does anyone know how to sum specific cells in a column that contains both numbers and text such as "N/A".

Basically, I want to sum all the cells in the column that are numbers.

If anyone has any suggestions on how to approach this problem, that would be much appreciated.

Thanks.

Iím pretty new to VBA and macros and Iím trying to delete rows if specific columns have certain values.
I tried different ways of doing it and I end up with a lot of headache.
I used other people codes but they were just to hard to understand whatís going on.
So I came up with this solution of solving the problem: I used Auto filter to select specific values and just deleted the rows.
Now Iím wondering why I donít see other people use this solution?
Is there a major flaws in solving the problem this way?
If somebody has better and easier to understand solution will be glad to try using it.

What I did here is auto filter for column D and value 99 and deleted it and then column D and value blank and deleted it and finally column F and value 0 and deleted it.

Sub Macro2()
'
' Macro2 Macro
' Macro recorded 10/16/2008 by Preferred User
'
Cells.Select
Selection.Sort Key1:=Range("D2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("A1").Select
Selection.AutoFilter
Selection.AutoFilter Field:=4, Criteria1:="99"
Rows("2:65536").Select
Selection.Delete Shift:=xlUp
Range("A1").Select
Selection.AutoFilter Field:=4
Selection.AutoFilter Field:=6, Criteria1:="0"
Rows("2:65536").Select
Selection.Delete Shift:=xlUp
Range("A1").Select
Selection.AutoFilter Field:=6
Selection.AutoFilter Field:=4, Criteria1:="="
Rows("2:65536").Select
Selection.Delete Shift:=xlUp
Range("A1").Select
Selection.AutoFilter Field:=4
'
End Sub

Hi,

I have a column with entries in them YES or NO. I would like to make a code that goes through the entire column, and if an entry is NO, I would like to delete it.

I've tried


	VB:
	
 DefineDelete() 
    Dim i As Integer 
    lastrow = Cells(65536, 1).End(xlUp).Row 
    For i = lastrow To 1 Step -1 
        If Left(CStr(Cells(i, 1).Value), 4)  "2006" Then 
            Rows(i).Delete 
        End If 
    Next 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
which I found through search but it does not work.

So I have a set of data like below, but with a much longer list.

xx
xx xx
Summary Data
xx xx Total 1204 xx
xx xx xx xx xx xx Summary Data xx xx xx xx xx xx Total 1500

What I need is to delete rows between the cell "Summary Data" and the next cell that contains value "Total ###", and to keep the cells in red. Note that the number after each "Total" is different on my list. I had a code but it deletes all the rows between first "Summary Data" and the last "Total ###" cell. anyone help is appreciated!

This is the code I have:


	VB:
	
 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Everyone,

I would really be grateful if someone could help me with the following:

In a column I have several rows (on average 100 rows) in which values like the following are defined: EP10000001A1. Thus, it looks something like the following

EP10000001A1
EP10000002A1
EP10000003A1
EP10000004A1

I would like to delete the last two characters in ever cell for every row. Hence, taking the above example all "A1" should be deleted so that it should looks like this:

EP10000001
EP10000002
EP10000003
EP10000004

As I said, on average I have a 100 rows in a column (sometimes up to 300 rows). Hence, I would really like to avoid doing this manually. How can I achieve this? Is there a standard Macro for this type of operation? (I must admit that I am new to VBA/creation of Macros).

Thank you in advance.

Best regards,
tony

Dear All ,

I have million rows ,

I have columns A to N ...the N may vary and i wish macro to self find the range using the range function and columns.count x1left function ,

Second in each of the rows ,i want macro to check A,B,C,D,F .if they are same ,i want them to be deleted using the following logic

If there are 5 rows X1,X2,X3,X4,X5 with identical data in A,B,C,D,F i want E column to be checked for each of this 5 rows ...keep the one with the largest string length ,i want rest all to be deleted .....in case data in cell E is same and repeats in all 5 entries ,i want all except 1 to be retained ...

While rows get deleted ,i want them to be deleted in entirety and not in range of A:F or etc ,i wish order to be maintained while tis process is carried out and that is why i have kept the last column as row number ,

i would wish someone to use loop and solve it for million entries ......if anyone can capture in ceryain cell rows getting deleted and duplicate entries ,it will be highly appreciated

i Hope that i have been clear enough outlying my requirement ?

Thanks ,

Amlan Dutta

I am new to this site and I think that it is wonderful. It has helped me immensly already. I created a post earlier, but with little response. I think that I have to be a little more specific with my problem.

I have a sheet with 1000's of records in it. I am trying to select certain records that start with a certain variable. For example in the attached I will try to select just the Load No's that start with "R". In my current macro I have it sort based on Load No, but the amount of records changes each month and producing a recorded macro just won't work. Is there a way to select just the R's??

I have done some research on this website as well and came across a similar post, but wasn't sure if this was the same problem. Take a look.

Similar Thread

Thanks to anyone that can help.

I am trying to delete rows froma spreadsheet where there is a 0 in column B of that row. For example spreadsheet starts like this:-

Allen 4 Allen
Dave 3 Dave
Phil 0
Pete 0
Andy 2 Andy

Once Macro run it will then look like this:-

Allen 4 Allen
Dave 3 Dave
Andy 2 Andy

Any suggestions please?

Good day all

Below i have a table that i would like to manipulate by deleting all rows
with 0.00h in its Remaining Duration column, to see it properly i would
suggest to cut and past it. I think i have done this action before but need
some help do do it this time.

Activity ID M/E Phase Activity Name Start Finish Visit Frequency Remaining
Duration
05-Mar-06 07-Mar-06 8.00h
Acme Station Elect 4.1 (a-i) 05-Mar-06 05-Mar-06 Q 2.00h
Acme Station Elect 4.1 (j-s) 05-Mar-06 06-Mar-06 HY 2.00h
Acme Station Elect 4.2.1 (a-k) 05-Mar-06 05-Mar-06 HY 0.50h
Acme Station Elect 4.2.2 (a-k) 05-Mar-06 05-Mar-06 HY 0.50h
Acme Station Elect 4.2.3 (a-j) 05-Mar-06 05-Mar-06 Q 0.50h
Acme Station Elect 4.2.4.1 (a-x) 06-Mar-06 06-Mar-06 HY 0.00h
Acme Station Elect 4.2.4.2 (y-xx) 06-Mar-06 06-Mar-06 HY 0.00h
Acme Station Elect 4.2.4.3 (a-w) 06-Mar-06 06-Mar-06 HY 0.00h
Acme Station Elect 4.2.4.4 (x-gg) 06-Mar-06 06-Mar-06 HY 0.00h
Acme Station Elect 4.3 (f-r) 06-Mar-06 06-Mar-06 HY 0.00h
Acme Station Elect 4.3.1 (a-h) 06-Mar-06 06-Mar-06 HY 0.50h
Acme Station Elect 4.3.2 (a-g) 06-Mar-06 06-Mar-06 HY 0.00h
Acme Station Elect 4.4.1.1 (a-f) 06-Mar-06 06-Mar-06 HY 0.08h
Acme Station Elect 4.4.1.2 a 06-Mar-06 06-Mar-06 HY 0.08h
Acme Station Elect 4.4.1.3 a 06-Mar-06 06-Mar-06 HY 0.08h

Thanks
Orly