Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro for deleting rows and serialising the remaing rows | Links and Linking in Excel | |||
Macro for deleting rows and serialising the remaing rows | Setting up and Configuration of Excel | |||
Macro for deleting rows and serialising the remaing rows | Excel Worksheet Functions | |||
Help!! I have problem deleting 2500 rows of filtered rows!!!! | Excel Discussion (Misc queries) | |||
deleting hidden rows so i can print only the rows showing?????? | Excel Worksheet Functions |