Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting empty rows in a worksheet
I want to use a macro to delete out all empty rows in a spreadsheet. There
are 1,700 rows and 10 columns. If all of the cells are blank in any of the rows, I want to delete that row. I am fairly new with VBA. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting empty rows in a worksheet
Dan
Is the entire row blank? If so can it not just be sorted? -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England www.nickhodge.co.uk HIS "danw" wrote in message ... I want to use a macro to delete out all empty rows in a spreadsheet. There are 1,700 rows and 10 columns. If all of the cells are blank in any of the rows, I want to delete that row. I am fairly new with VBA. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting empty rows in a worksheet
Hi danw
Try this one Sub Example2() Dim Lrow As Long Dim CalcMode As Long Dim ViewMode As Long Dim StartRow As Long Dim EndRow As Long With Application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False End With ViewMode = ActiveWindow.View ActiveWindow.View = xlNormalView With ActiveSheet .DisplayPageBreaks = False StartRow = 1 EndRow = 1700 For Lrow = EndRow To StartRow Step -1 If Application.CountA(.Rows(Lrow)) = 0 Then .Rows(Lrow).Delete 'This will delete the row if the whole row is empty (all columns) Next End With ActiveWindow.View = ViewMode With Application .ScreenUpdating = True .Calculation = CalcMode End With End Sub There are more examples here http://www.rondebruin.nl/delete.htm -- Regards Ron de Bruin http://www.rondebruin.nl "danw" wrote in message ... I want to use a macro to delete out all empty rows in a spreadsheet. There are 1,700 rows and 10 columns. If all of the cells are blank in any of the rows, I want to delete that row. I am fairly new with VBA. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting empty rows in a worksheet
Dan
Or you could use something like this, with blanks in A Sub delRows() Range("A:A").SpecialCells(xlCellTypeBlanks).Entire Row.Delete End Sub -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England www.nickhodge.co.uk HIS "danw" wrote in message ... I want to use a macro to delete out all empty rows in a spreadsheet. There are 1,700 rows and 10 columns. If all of the cells are blank in any of the rows, I want to delete that row. I am fairly new with VBA. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Deleting empty rows | New Users to Excel | |||
deleting empty rows | Excel Discussion (Misc queries) | |||
Deleting empty rows | Excel Programming | |||
Deleting non empty rows | Excel Programming | |||
Deleting empty rows | Excel Programming |