Filter out rows of information in a list via VBA

[James C]

I have a list in a worksheet which has a column headings and in one column
contains text data. In the rows the data consists of items such E10, V19 and
U27.The rows in the list change frequenlty, as new data is added, so the
column length varies.

I want to get rid of any item that does not have the prefix U in front of it
and include this in part of an existing VBA statement.

Thanks

 

[Dave Peterson]

Dim myCell as range
dim myRng as range
dim delRng as range

with worksheets("Somesheetnamehere")
set myrng = .range("A1", .cells(.rows.count,"A").end(xlup))
end with

for each mycell in myrng.cells
if lcase(left(mycell.value,1)) = lcase("u") then
'keep it
else
if delrng is nothing then
set delrng = mycell
else
set delrng = union(mycell, delrng)
end if
end if
next mycell

if delrng is nothing then
'nothing to delete
else
delrng.entirerow.delete
end if

========
Untested, uncompiled. Watch for typos.

I used column A and deleted the entire row. You may have to change the code.

 

[Gord Dibben]

Sub Hide_U_Rows()
Dim RngCol As Range
Dim i As Range
Set RngCol = Range("B1", Range("B" & Rows.Count). _
End(xlUp).Address)
For Each i In RngCol
If Left(i.Value, 1) = "U" Then _
i.EntireRow.Hidden = True
' i.EntireRow.Delete
End If
Next i
End Sub


Gord Dibben MS Excel MVP

 

[James C]

Dave thank you for your response.

I am having difficulity with running your suggestion. I hope you may be able
to still help.

The with worksheets was out of range so I deleted this as I put your macro
into part of a current macro.I am hoping this is not a problem.
The
set myrng = .range("A1", .cells(.rows.count,"A").end(xlup))
is coming up as a complie error and I do not know how to orrect this.
Can you help further?

Thank you James

 

[James C]

Dave hi again had a second go using a new workbook.

The macro works fine but deletes my first row which has my column headings in.
Can you advise how to overcome this.

Thanks

 

[James C]

Thank you for your response.

I am have copied and pasted your suggestion into my current macro and am
having no luck with it working. It comes up with an error message End If
Without Block If.

Tried your macro in a new workbook and it appeasr to hide or delete U codes
leaving the codes that I do not require.

Can you help further?

thanks

 

[Dave Peterson]

First, you found that you have to change the first line to the name of the
sheet.

And if you change the second line to start in A2 (not A1), you'll avoid row 1.

with worksheets("Somesheetnamehere")
set myrng = .range("A1", .cells(.rows.count,"A").end(xlup))
end with

Dave hi again had a second go using a new workbook.

The macro works fine but deletes my first row which has my column headings in.
Can you advise how to overcome this.

Thanks

 

[Dave Peterson]

Remove the space-underscore at the end of this line:

If Left(i.Value, 1) = "U" Then _

And Gord left two options in the code. You can delete the row or hide the row.
The delete is commented out. Use the one you want and delete the line you don't
want.

 

[Gord Dibben]

Bit of a mis-read on my part.

Try this revised version.

Option Compare Text
Sub Delete_NonU_Rows()
Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long
FirstRow = 1
LastRow = Cells(Rows.Count, "B").End(xlUp).Row
For iRow = LastRow To FirstRow Step -1
If Left(Cells(iRow, "B").Value, 1) <> "U" Then
Rows(iRow).EntireRow.Delete
End If
Next
End Sub


Gord