Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
automate delete rows without data
It is tediious to manually delete rows that are empty and I was wondering if
there was a way to automatically delete empty rows either by using a function or creating a macro or if there was any other easier solution. Thank you. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
automate delete rows without data
"Amir" wrote: It is tediious to manually delete rows that are empty and I was wondering if there was a way to automatically delete empty rows either by using a function or creating a macro or if there was any other easier solution. Thank you. Found something from John James... Works great...Thanks John... If you want to delete blank rows on your datasheet, here's a routine which does this: Sub DeleteEmptyRows() LastRow = ActiveSheet.UsedRange.Row - 1 + _ ActiveSheet.UsedRange.Rows.Count Application.ScreenUpdating = False For r = LastRow To 1 Step -1 If Application.CountA(Rows(r)) = 0 Then Rows(r).Delete Next r End Sub If your formulae are slowing down your worksheet, then maybe try getting the same result but without formulae. For instance if you filter your data on your datasheet, then you'll be able to copy the filtered range onto another sheet. If this meets your needs, you could look at attaching this filter & paste process to a macro/button. -- John James ------------------------------------------------------------------------ John James's Profile: http://www.excelforum.com/member.php...o&userid=32690 View this thread: http://www.excelforum.com/showthread...hreadid=532460 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
automate delete rows without data
On Aug 19, 4:21*pm, Amir wrote:
"Amir" wrote: It is tediious to manually delete rows that are empty and I was wondering if there was a way to automatically delete empty rows either by using a function or creating a macro or if there was any other easier solution. Thank you. Found something from John James... Works great...Thanks John... If you want to delete blank rows on your datasheet, here's a routine which does this: Sub DeleteEmptyRows() LastRow = ActiveSheet.UsedRange.Row - 1 + _ ActiveSheet.UsedRange.Rows.Count Application.ScreenUpdating = False For r = LastRow To 1 Step -1 If Application.CountA(Rows(r)) = 0 Then Rows(r).Delete Next r End Sub If your formulae are slowing down your worksheet, then maybe try getting the same result but without formulae. *For instance if you filter your data on your datasheet, then you'll be able to copy the filtered range onto another sheet. *If this meets your needs, you could look at attaching this filter & paste process to a macro/button. -- John James ------------------------------------------------------------------------ John James's Profile:http://www.excelforum.com/member.php...o&userid=32690 View this thread:http://www.excelforum.com/showthread...hreadid=532460 For a non macro way, you can put in a new index column and put 1 and 2 in the top two cells. Fill down to the bottom of the data. Sort everything on another column. All the blanks go together at the bottom. Delete them all at once. Sort on the index column. and delete it. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
automate delete rows without data
You could select a column and F5SpecialBlanksOK
EditDeleteEntire Row Gord Dibben MS Excel MVP On Tue, 18 Aug 2009 20:44:01 -0700, Amir wrote: It is tediious to manually delete rows that are empty and I was wondering if there was a way to automatically delete empty rows either by using a function or creating a macro or if there was any other easier solution. Thank you. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I identify matching rows of data and delete them both? | Excel Discussion (Misc queries) | |||
Delete specific rows of data | New Users to Excel | |||
How do I delete all rows that contain no red highlighted data? | Excel Discussion (Misc queries) | |||
Delete rows with no data | Setting up and Configuration of Excel | |||
Macro to delete rows with same data | Excel Worksheet Functions |