Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Removing Blank Rows for Excel VB Newb
How can I create a macro or code to delete rows? I need it to delete all the
rows with absolutely nothing in them, so basing it on one column won't work. Also, a lot of the cells are merged throughout. I am still a little confused about the different drop down choices when looking at the code area, so if you would please explain what area I would need to copy to (if you post code). Is it possible to have it where this macro or code is available whenever I open excel? I do a lot of on-line reporting in Excel format and it would be a great help to have the macro available when I first open the reports. Thanks for your help, Joel |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Removing Blank Rows for Excel VB Newb
Hi Joel
You copy the code in a standard module http://www.rondebruin.nl/code.htm Try this macro for the data on the activesheet More on this page http://www.rondebruin.nl/delete.htm Sub Loop_Example() Dim Firstrow As Long Dim Lastrow As Long Dim Lrow As Long Dim CalcMode As Long Dim ViewMode As Long With Application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False End With 'We use the ActiveSheet but you can replace this with 'Sheets("MySheet")if you want With ActiveSheet 'We select the sheet so we can change the window view .Select 'If you are in Page Break Preview Or Page Layout view go 'back to normal view, we do this for speed ViewMode = ActiveWindow.View ActiveWindow.View = xlNormalView 'Turn off Page Breaks, we do this for speed .DisplayPageBreaks = False 'Set the first and last row to loop through Firstrow = .UsedRange.Cells(1).Row Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row 'We loop from Lastrow to Firstrow (bottom to top) For Lrow = Lastrow To Firstrow Step -1 If Application.CountA(.Rows(Lrow)) = 0 Then .Rows(Lrow).Delete Next Lrow End With ActiveWindow.View = ViewMode With Application .ScreenUpdating = True .Calculation = CalcMode End With End Sub -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Jase4now" wrote in message ... How can I create a macro or code to delete rows? I need it to delete all the rows with absolutely nothing in them, so basing it on one column won't work. Also, a lot of the cells are merged throughout. I am still a little confused about the different drop down choices when looking at the code area, so if you would please explain what area I would need to copy to (if you post code). Is it possible to have it where this macro or code is available whenever I open excel? I do a lot of on-line reporting in Excel format and it would be a great help to have the macro available when I first open the reports. Thanks for your help, Joel |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Removing Blank Rows in an excel spreadsheet | Excel Discussion (Misc queries) | |||
Removing blank rows | Excel Programming | |||
Removing blank rows | Excel Programming | |||
Copying and pasting a worksheet to a blank and removing blank rows | Excel Programming | |||
Removing Blank Rows? | Excel Programming |