Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Delete row if all cells are blank, Excel 2000 & 2003
Hello,
I have a worksheet with 1061 rows and 7 columns of data. Example: column A B C D E aa bb cc vv mm blank row ss I'm need a macro to delete only the blank rows. I've tried the following macro but it deletes a row when it finds a empty cell. Can this macro be modified to delete a row with all empty cells only? Sub DeleteRowsExampleB() 'Delete Selected Rows with specified data Dim c As Range Dim x As Range Set x = Selection For Each c In x If c.Value = "" Then c.EntireRow.Delete End If Next c End Sub Thank you for your help, jfcby |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Delete row if all cells are blank, Excel 2000 & 2003
Try this one
For more info see 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 wrote in message ... Hello, I have a worksheet with 1061 rows and 7 columns of data. Example: column A B C D E aa bb cc vv mm blank row ss I'm need a macro to delete only the blank rows. I've tried the following macro but it deletes a row when it finds a empty cell. Can this macro be modified to delete a row with all empty cells only? Sub DeleteRowsExampleB() 'Delete Selected Rows with specified data Dim c As Range Dim x As Range Set x = Selection For Each c In x If c.Value = "" Then c.EntireRow.Delete End If Next c End Sub Thank you for your help, jfcby |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using a function to delete blank cells | Excel Worksheet Functions | |||
In Office 2000, How do I delete multiple blank rows at one time? | Excel Worksheet Functions | |||
Macro to Delete blank cells | Excel Worksheet Functions | |||
unlocked cells in excel 2000 now locked when opened in 2003 why? | Excel Discussion (Misc queries) | |||
In Excel 2000, Cannot edit cells in worksheet created in 2003? | Excel Discussion (Misc queries) |