Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How can I globally delete empty cells or rows in different places
I pasted a long list (report) from word pad into EXCEL. THis list has lots
of empty rows spread throughout this single column list. Can I globally delete all empty rows? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How can I globally delete empty cells or rows in different places
Select the col. from which you would like to delete the empty rows. Click on
"EDIT -- Go To -- Special -- Blanks -- OK". This step will highlight only the blank cells in the selected col. You can now delete all the blank rows at once. sk "McLeod" wrote: I pasted a long list (report) from word pad into EXCEL. THis list has lots of empty rows spread throughout this single column list. Can I globally delete all empty rows? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How can I globally delete empty cells or rows in different places
The one worded-response I can give you is: macro. Open the file where you need to erase the rows, open the Macro module by using the following steps: Tools Macro Visual Basic Editor In the Visual Basic window go: Insert Module Then, copy/paste the following into the 'Module' window that appears: Code: -------------------- Sub DeleteEmptyRows() Dim LastRow As Long, r As Long 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 Application.ScreenUpdating = True End Sub -------------------- Finally, press F5. NOTE: If you do this in the file, Excel will give you a warning about Macros EVERY time you open the file. Alternatively, you can enter the code in a blank workbook (one that you will not save), switch to the file where you need it, then back to the module window and press F5. -- ArenaNinja ------------------------------------------------------------------------ ArenaNinja's Profile: http://www.excelforum.com/member.php...o&userid=33624 View this thread: http://www.excelforum.com/showthread...hreadid=543108 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
How can I globally delete empty cells or rows in different places
The one worded-response I can give you is: macro. Open the file where you need to erase the rows, open the Macro module by using the following steps: Tools Macro Visual Basic Editor In the Visual Basic window go: Insert Module Then, copy/paste the following into the 'Module' window that appears: Code: -------------------- Sub DeleteEmptyRows() Dim LastRow As Long, r As Long 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 Application.ScreenUpdating = True End Sub -------------------- Finally, press F5. NOTE: If you do this in the file, Excel will give you a warning about Macros EVERY time you open the file. Alternatively, you can enter the code in a blank workbook (one that you will not save), switch to the file where you need it, then back to the module window and press F5. -- ArenaNinja ------------------------------------------------------------------------ ArenaNinja's Profile: http://www.excelforum.com/member.php...o&userid=33624 View this thread: http://www.excelforum.com/showthread...hreadid=543108 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
resetting last cell | Excel Discussion (Misc queries) | |||
How can I convert empty strings to empty cells? | Excel Discussion (Misc queries) | |||
In a protected worksheet allow users to delete rows | Excel Worksheet Functions | |||
Counting empty cells within a range of cells | New Users to Excel | |||
Counting empty cells within a range of cells | New Users to Excel |