Deleting Rows
 

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!


--
STEVEB
------------------------------------------------------------------------
STEVEB's Profile: http://www.excelforum.com/member.php...fo&userid=1872
View this thread: http://www.excelforum.com/showthread...hreadid=483143

Deleting Rows
 

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

--
Regards,
Tom Ogilvy


"STEVEB" wrote in
message ...

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!


--
STEVEB
------------------------------------------------------------------------
STEVEB's Profile:
http://www.excelforum.com/member.php...fo&userid=1872
View this thread: http://www.excelforum.com/showthread...hreadid=483143

Deleting Rows
 

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

--
STEVE
-----------------------------------------------------------------------
STEVEB's Profile: http://www.excelforum.com/member.php...nfo&userid=187
View this thread: http://www.excelforum.com/showthread.php?threadid=48314

Deleting Rows
 

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!!


--
STEVEB
------------------------------------------------------------------------
STEVEB's Profile: http://www.excelforum.com/member.php...fo&userid=1872
View this thread: http://www.excelforum.com/showthread...hreadid=483143

Deleting Rows
 

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

--
Regards,
Tom Ogilvy




"STEVEB" wrote in
message ...

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!!


--
STEVEB
------------------------------------------------------------------------
STEVEB's Profile:
http://www.excelforum.com/member.php...fo&userid=1872
View this thread: http://www.excelforum.com/showthread...hreadid=483143

Deleting Rows
 

Thanks Tom,

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


--
STEVEB
------------------------------------------------------------------------
STEVEB's Profile: http://www.excelforum.com/member.php...fo&userid=1872
View this thread: http://www.excelforum.com/showthread...hreadid=483143