Delete blank row from a particular cell to end of row

[AngelB]

Hi All

I am using Excel 2003 (SP2).

If a particular cell in my worksheet is blank, then the entire row will be blank and I wish to delete it. I found the following line of code posted by anotherhiggins in a separate thread:

Columns("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete

which would quite rightly delete the entire row where a blank cell was found in column A. The twist in my problem is that I have a couple of columns that I wish to do this with, one of which is further across the worksheet. I do not wish to delete the entire row in this case as there may be data in the preceding columns. In this case, I would like to delete the row from the searched column to the end of the row, and shift the remaining rows up. I know how to do the upward shift once the row is deleted e.g.

Range("E3:IV3").Delete Shift:=xlUp

but I think the trick lies in identifying the row number of the blank cell, and I'm not sure how to retrieve that.

Can someone point me in the right direction?

Many Thanks


Geraint

The lights are on but nobody's home, my elevator doesn't go to the top. I'm not playing with a full deck, I've lost my marbles. Barenaked Ladies - Crazy

 

[ck1999]

you can use

dim vrowlast
dim counter
vrowlast=range("E60000").end(xlup)row ' replace 60000 with number of cells allowed in your version of xl or leave if you do not have more than this many rows

for counter = 3 to vrowlast
if range("e"&counter)="" then
Range("E" & counter,"IV" & counter").Delete Shift:=xlUp

next counter

ck1999

 

[ck1999]

Change the loop so it goes backward

for counter = vrowlast to 3 step -1

sorry about that

ck1999

 

[AnotherHiggins]

My first suggestion is not to store multiple tables side-by-side. Can you move your other data? Preferably to its own sheet. If that's not possible, then at least below the first table.

If you can move the table, then the original code you quoted will work.

Otherwise keep reading:

If the only time there are ever any blank cells in these columns is when the entire row (for this section) is blank, then you can just change the columns you are looking at in the first place.

Let's say this section of data is in columns E:K. Then this would work:

Columns("E:K").SpecialCells(xlCellTypeBlanks).Delete shift:=xlUp

If you have to base the decision to delete on a single column, then consider just sorting. If you sort this section of columns, you can sort all of the empty rows to the bottom!

Columns("E:K").sort Key1:=Range("E2"), Order1:=xlAscending, Header:=xlYes

Now all of the empty cells are at the bottom and you are left with non-blank rows.


[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.