Easy Macro Question

[seanbanks]

Hi all,

I'm trying to figure out a macro and I'm sure its an easy fix but I can't figure it out. I need to delete all rows when a specific column has a blank cell. The problem is that column isn't always the same. But it always has the same name, "keep_it".

In summary, I need to find a column "keep_it" and delete all rows in which "keep_it" has blank values, but in some worksheets "keep_it" is in column C (for example) and in others it is in a different location.

Thank you in advance,
Sean

 

[Diablo II]

sent excel 2007 has 200,000 row

 

[Jonmo1]

Keep_It exists in Row 1 in an unkown column right?

This will do it on the active sheet.
Need more details about where the sheets are if you want to make it loop over several sheets...

Sub Delete_Keep_It()
 
MyCol = Application.Match("Keep_It",Range("1:1"),0)
Columns(MyCol).SpecialCells(xlcelltypeblanks).EntireRow.Delete
 
End Sub

 

[Diablo II]

i know why you got that error the code was for the columns being namded keep_it click on your column and in the name box in the left hand side type in Keep_it and hit enter . then the code will work. click on the "I" column in the workbook i sent you you will see in the left side a dropdown boxes with it in it.

 

[seenfresh]

sean

try this code: It will find the column where "keep_it" exists in an active sheet and delete all records that do not have the value "keep_it" in such column.

Hope this helps!

Sub test_()

Dim c As Variant

    Dim cell1, cell2, rng, newrng As Range
    Dim frstrow, lstrow As Long
    
    frstrow = ActiveSheet.UsedRange.Rows.Count
    lstrow = ActiveSheet.UsedRange.Columns.Count
    
    Set cell1 = Cells(1, 1)
    Set cell2 = Cells(frstrow, lstrow)
    Set rng = Range(cell1, cell2)
    
For Each c In rng

If c.Value = "keep_it" Then

mycolumn = c.Column

End If
Next c

Set newrng = Range(Cells(frstrow, mycolumn), Cells(lstrow, mycolumn))

For iRow = frstrow To lstrow Step -1

If Cells(iRow, mycolumn).Value <> "keep_it" Then

Cells(iRow, mycolumn).EntireRow.Delete

End If

Next iRow
End Sub

 

[seanbanks]

Thank you all for the responses. jonmo1's was the easiest solution. More importantly, it worked! Thank you!

Best,
Sean