Excel 2000 VBA problem

  • Thread starter Thread starter brian.jermain
  • Start date Start date
B

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
 
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!
 
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
 
Yes. You could use "with activesheet" to work with whatever sheet is active.

And I had a typo.

Use:
.rows(irow).delete

Sorry.
 
Back
Top