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

 

[seenfresh]

Hi Sean

Your question is a little fuzzy to understand let me know if my understanding below is accurate.

You have several sheets in a workbook in which you need to cycle through each worksheet and find the column where "keep it" exists. Once you determine the column you would like to cycle through all records in this column and particular worksheet and delete all records where no values exists in this column...

 

[seanbanks]

That is correct. The different worksheets are in several workbooks. Other than that, you've got it correct.

 

[Diablo II]

this will work on any column you pick and will delet any empty row and move it up.

Sub test()
 Dim theRange As Range
 Dim nCells As Integer
 Dim I As Integer
   Set theRange = Selection
   nCells = theRange.cells.Count
   For I = nCells To 1 Step -1
     If theRange.cells(I).Value = 0 Then
        theRange.cells(I).EntireRow.Delete
     End If
   Next
End Sub

 

[jfklbj]

I'm not sure how your data is arranged, but would it be more effective to copy all the data that you want to keep to a new worksheet or workbook?

 

[seanbanks]

Thanks, Diablo. But I'm not sure how this helps. It seems to just delete one row at a time? Maybe I'm doing something wrong.

 

[seanbanks]

hi jfklbj... no i'd like to keep the data in the same worksheet/

 

[Diablo II]

go to the last entery and click and go to the top pick from bottom to the top then run.

 

[seanbanks]

So I have to highlight all of the data. I see. Ok, is there code that I can add to that which will highlight all of the data for you? Some of these worksheets have 10,000+ rows.

 

[Diablo II]

working on that for you.