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

 

[seanbanks]

I've been searching online but not coming up with anything.

 

[Diablo II]

try this

Sub test()
 Dim theRange As Range
 Dim nCells As Integer
 Dim I As Integer
Range("keep_it").Activate
  Range(ActiveCell, cells(65536, ActiveCell.Column) _
 .End(xlUp)).Select
   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

 

[seanbanks]

Run-time error '1004'

Method 'Range' of object ' _Global' failed

 

[Diablo II]

it works for me i named the column Range keep_it, what excel V you have. i can send you the work book to look at it.

 

[seanbanks]

Windows 2007

 

[Diablo II]

excel 7 the new one? if so you will need to fix the max i am useing excel 2000. the max rows is 65563.

 

[seanbanks]

looks like adding this line at the beginning of your first macro will work...

Range(Selection, Selection.SpecialCells(xlLastCell)).Select

?

 

[seanbanks]

Nope... its just deleting all of my data. Fun times.

 

[Diablo II]

you wont the work book to look at?

 

[seanbanks]

yes, that'd be great. sean.banks@ucdenver.edu