Newbie Needs Help with Simple Macro

[promsi]

I have a simple macro I need some help with.

I want to spin through a row range of cells (O2,X2) on a specific page. As I spin through, I want to check a specific cell in that range (S2) and see if that cell is either NULL or has a zero (0). If either is correct (NULL or 0) then delete the range and move the cells below up.

I am not familiar with the VB/Excel syntax but here is what I was starting with and obviously does not work.

Any help would be greatly appreciated.

Dim f_RowCount
f_RowCount = 275
For i = 2 To f_RowCount
f_cell1 = "0" & i
f_cell2 = "X" & i
f_checkcell = "S" & i
If IsNull(f_checkcell) Or (f_checkcell = 0) Then
Sheets("Sheet2").Select
Range(f_cell1, f_cell2).Select
Selection.Delete Shift:=x1Up
End If

Next i

 

[Andrew Poulsom]

Welcome to the Board.

In what way doesn't it work? When deleting ows you need to loop backwards, otherwise some may be missed:

For i = f_RowCount To 2 Step -1

 

[ravi4ever]

if your macro stops at this line

Selection.Delete Shift:=x1Up

then change it to

Selection.Delete Shift:=xlUp

rest I do not have much idea on it..

 

[promsi]

First of all the If statement that checks for NULL() is not working correctly - as I step through it it never goes into the IF statement even when the S cell is NULL

Then when I force it into the IF statement it fails at the Range("f_cell1&:&f_cell2").Select line.

It says "Run time error '1004 Method 'Range' of object '_Global' failed"

 

[Andrew Poulsom]

Try:

IsEmpty(f_checkcell)

and:

Range(f_cell1 & ":" & f_cell2).Select

 

[promsi]

Sorry - IsEmpty still doesn't work.

Same error on the Range syntax

 

[Andrew Poulsom]

That's because:

f_cell1 = "0" & i

should be:

f_cell1 = "O" & i

 

[promsi]

ARG!!! - I am such an idiot - sorry to bother you for such a stupid typing error.

Ok - now to the IsEmpty problem..... Any ideas on that one?

 

[Andrew Poulsom]

What's in the cell, a formula that returns ""? If so try:

If Len(f_checkcell.Value) = 0

or:

If f_checkcell.Value = ""

 

[promsi]

Ok - I like the LEN() function but now it gives an error 424 Object Required