Excel 2000 VBA problem

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

brian.jermain@googlemail.com - 07 Nov 2006 17:25 GMT

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

Reply to this Message

Dave Peterson - 07 Nov 2006 18:14 GMT

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!

> 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
[quoted text clipped - 15 lines]
> Brian
> Scotland

Signature

Dave Peterson

Reply to this Message

BJ&theBear - 07 Nov 2006 19:17 GMT

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

> You have a couple of ways. One you can loop from the bottom up and delete the
> rows as you need.
[quoted text clipped - 38 lines]
> > Brian
> > Scotland

Reply to this Message

Dave Peterson - 07 Nov 2006 19:41 GMT

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

And I had a typo.

Use:
.rows(irow).delete

Sorry.

> Dave
>
[quoted text clipped - 60 lines]
> >
> > Dave Peterson

Signature

Dave Peterson

Reply to this Message