Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Easy Question
I need to check each row of a database to ensure it is not blank (this is
performed after importing data from an external source). If the cell is blank, I need to delete the entire row. The problem I'm having is if two blank rows are next to each other. The routine I use does not delete the second row, because of the deletion of the first one. How do I 'reset' the variable to the row before the deletion, so that the routine catches the second blank row? For Each cl In Range("A2:A" & Range("A65500").End(xlUp).Row).Cells If cl = Empty Then cl.EntireRow.Delete End If Next |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Easy Question
Try this statement to delete instead.
SpecialCells(xlCellTypeBlanks).Delete Shift:=xlUp "Posse John" wrote: I need to check each row of a database to ensure it is not blank (this is performed after importing data from an external source). If the cell is blank, I need to delete the entire row. The problem I'm having is if two blank rows are next to each other. The routine I use does not delete the second row, because of the deletion of the first one. How do I 'reset' the variable to the row before the deletion, so that the routine catches the second blank row? For Each cl In Range("A2:A" & Range("A65500").End(xlUp).Row).Cells If cl = Empty Then cl.EntireRow.Delete End If Next |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Easy Question
Select your data range and run this
Sub Button1_Click() Dim i As Long With Application .Calculation = xlCalculationManual .ScreenUpdating = False For i = Selection.Rows.Count To 1 Step -1 If WorksheetFunction.CountA(Selection.Rows(i)) = 0 Then Selection.Rows(i).EntireRow.Delete End If Next i .Calculation = xlCalculationAutomatic .ScreenUpdating = True End With End Sub "Posse John" wrote: I need to check each row of a database to ensure it is not blank (this is performed after importing data from an external source). If the cell is blank, I need to delete the entire row. The problem I'm having is if two blank rows are next to each other. The routine I use does not delete the second row, because of the deletion of the first one. How do I 'reset' the variable to the row before the deletion, so that the routine catches the second blank row? For Each cl In Range("A2:A" & Range("A65500").End(xlUp).Row).Cells If cl = Empty Then cl.EntireRow.Delete End If Next |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Easy Question
Thank you both...didn't know about the 'specialcells' function. NICE.
Ended up using: Columns("A:A").SpecialCells(xlCellTypeBlanks).Enti reRow.Delete |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Easy question | Excel Discussion (Misc queries) | |||
VBA help €“ an easy question | Excel Programming | |||
new user with easy question? not easy for me | New Users to Excel | |||
easy question | Excel Worksheet Functions | |||
Probably the most easy question here... | Excel Programming |