Deleting Rows

[STEVEB]

Hi,

I have a sheet that has blank rows at the beginning of the spreadsheet.
For example this week rows 1 thru 3 are empty before data is entered in
Row 4. Last week rows 1 thru 8 were empty and sometimes there are no
empty rows before data is entered (Data in row 1).

Since the number of empty rows varies each month, is there a way to
delete the entire empty row(s), if any, before data is entered in
column A.

Any help would be greatly appreciated!

 

[Tom Ogilvy]

Dim rng as Range
on error resume next
set rng = columns(1).specialcells(xlblanks)
On error goto 0
if not rng is nothing then
if rng.Row = 1 then
rng.Areas(1).EntireRow.Delete
end if
end if

 

[STEVEB]

Hi Tom,

Thanks for your help, I appreciate it!

I tried your example & the active cell moves to A1, however, it doe
not delete rows 1-3. Have I missed something?

Thank

 

[STEVEB]

Tom,


I hope this helps:

The code worked when I "cleared the contents" rows 1-3, prior to
running the Macro. The reason the # of rows empty changes each week is
due to downloading various data. Is this an issue?

Even though there is no data in rows 1-3, for some reason, Excel is
recognizing the cells as "not blank".

Do you have any sugesstions? Thanks again!!

 

[Tom Ogilvy]

You said they were blank, but apparently you are pasting something into them
so they are not blank.

You can probably use something like

Sub DeleteRows()
Do While Len(Trim(Range("A1"))) = 0
Rows(1).Delete
Loop
End Sub

 

[STEVEB]

Thanks Tom,

Everything worked great! I really appreciate your help!!!