Excel 2000 VBA problem

[brian.jermain]

Can someone please advise me how I would write a VBA routine to scroll
through each row of a spreadsheet and delete the row if one particular
column value <>"" . I should mention that there is a formula in this
column - IF (H1= "","",networkdays etc etc. I am trying to keep all
the lines where a formula evaluates to "" and delete all the rows where
it has been evaluated

I cannot find a methodology to increment each row to the bottom of the
sheet.

I also wish to know whether Excel 2000 automatically adjusts the
formulas after the row above has been deleted

Does that make any sense?

Any help would be appreciated

Brian
Scotland

 

[Dave Peterson]

You have a couple of ways. One you can loop from the bottom up and delete the
rows as you need.

Or you could apply data|Filter|autofilter to show the "" cells. Then delete the
visible rows.


dim iRow as long
dim FirstRow as long
dim LastRow as long

with worksheets("sheet1")
firstrow = 2 'headers in row 1?
lastrow = .cells(.rows.count,"A").end(xlup).row 'I used column A

for irow = lastrow to firstrow step -1
if .cells(irow,"A").value = "" then
.rows.delete
end if
next irow
end with

Watch for typos!

 

[BJ&theBear]

Dave

Thanks for the help but I am having a problem - the only change that I
have made was to look at column C for the "" entry so my only change
was

if .cells(irow,"C").value = "" then
.rows.delete

but it deleted every row even though in my test data there are 4
entries with ""

Could:- "with worksheets("sheet1")" be replaced with "with activesheet"

Thanks

brian

 

[Dave Peterson]

Yes. You could use "with activesheet" to work with whatever sheet is active.

And I had a typo.

Use:
.rows(irow).delete

Sorry.