Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
deleting blank rows for up to 60000 rows of data
I have worksheets with up to 60000 rows in one column I have tried using the
following macro from this site and it does not do anything. Any ideas?: Sub Sonic() Dim i As Long With Application .Calculation = xlCalculationManual .ScreenUpdating = False Lastrow = ActiveSheet.UsedRange.Rows.Count For i = Lastrow To 1 Step -1 If WorksheetFunction.CountA(Rows(i)) = 0 Then Rows(i).EntireRow.Delete End If Next i .Calculation = xlCalculationAutomatic .ScreenUpdating = True End With End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
deleting blank rows for up to 60000 rows of data
Hi,
The code works fine for me. One point is the entire row must be empy before it will be selected for deletion. i.e a formula thta returns a null string and looks empty won't be deleted. Sub Sonic() Dim i As Long With Application .Calculation = xlCalculationManual .ScreenUpdating = False Lastrow = ActiveSheet.UsedRange.Rows.Count For i = Lastrow To 1 Step -1 If WorksheetFunction.CountA(Rows(i)) = 0 Then Rows(i).EntireRow.Delete End If Next i .Calculation = xlCalculationAutomatic .ScreenUpdating = True End With End Sub Mike "gbpg" wrote: I have worksheets with up to 60000 rows in one column I have tried using the following macro from this site and it does not do anything. Any ideas?: Sub Sonic() Dim i As Long With Application .Calculation = xlCalculationManual .ScreenUpdating = False Lastrow = ActiveSheet.UsedRange.Rows.Count For i = Lastrow To 1 Step -1 If WorksheetFunction.CountA(Rows(i)) = 0 Then Rows(i).EntireRow.Delete End If Next i .Calculation = xlCalculationAutomatic .ScreenUpdating = True End With End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
deleting blank rows for up to 60000 rows of data
Noe,
I hope this is work for you Sub Delete_Rows() Range("C1").Select a = 0 Do While a < 1 If ActiveCell.Value = Empty Then Selection.EntireRow.Delete Else End If If ActiveCell.Value = "BLANK" Then a = 1 Else ActiveCell.Offset(1, 0).Activate End If Loop Application.CutCopyMode = False Range("A2").Select End Sub "gbpg" wrote: I have worksheets with up to 60000 rows in one column I have tried using the following macro from this site and it does not do anything. Any ideas?: Sub Sonic() Dim i As Long With Application .Calculation = xlCalculationManual .ScreenUpdating = False Lastrow = ActiveSheet.UsedRange.Rows.Count For i = Lastrow To 1 Step -1 If WorksheetFunction.CountA(Rows(i)) = 0 Then Rows(i).EntireRow.Delete End If Next i .Calculation = xlCalculationAutomatic .ScreenUpdating = True End With End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
deleting blank rows for up to 60000 rows of data
Delting Rows one at a time is extremely slow. Why don't you sort the rows and the blank cells will simply move to the bottom. To get the data back to the original postion add an inedx column like the code below the code may seem a lot but it will run in seconds instead of the other posted code taking minutes. Sub SortBlanks() LastRow = Range("A" & Rows.Count).End(xlUp).Row 'add count 1, 2 to column IV Range("IV1:IV" & LastRow).Formula = "=Row()" 'change formula to value Range("IV1:IV" & LastRow).Copy Range("IV1:IV" & LastRow).PasteSpecial _ Paste:=xlPasteValues 'sort using column A Rows("1:" & LastRow).Sort _ header:=xlNo, _ key1:=Range("A1"), _ order1:=xlAscending 'now find new Last row LastRow = Range("A" & Rows.Count).End(xlUp).Row 'sort using column IV Rows("1:" & LastRow).Sort _ header:=xlNo, _ key1:=Range("IV1"), _ order1:=xlAscending 'delete column IV Columns("IV").Delete End Sub -- joel ------------------------------------------------------------------------ joel's Profile: 229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=165245 Microsoft Office Help |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
deleting blank rows for up to 60000 rows of data
Hi
Try this one liner, just change the column letter to suit: Sub DeleteEmptyRows() Columns("B").SpecialCells(xlCellTypeBlanks).Entire Row.Delete End Sub Regards, Per On 27 Dec., 09:26, gbpg wrote: I have worksheets with up to 60000 rows in one column I have tried using the following macro from this site and it does not do anything. Any ideas?: Sub Sonic() Dim i As Long With Application * * .Calculation = xlCalculationManual * * .ScreenUpdating = False * * Lastrow = ActiveSheet.UsedRange.Rows.Count * * For i = Lastrow To 1 Step -1 * * * * If WorksheetFunction.CountA(Rows(i)) = 0 Then * * * * * * Rows(i).EntireRow.Delete * * * * End If * * Next i * * * * .Calculation = xlCalculationAutomatic * * * * .ScreenUpdating = True * * End With End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Deleting blank rows which contain blank drop-down list boxes | Excel Programming | |||
Deleting 63,886 Blank Rows Under My Data | Excel Discussion (Misc queries) | |||
Deleting blank rows | Excel Programming | |||
Deleting blank rows | Excel Programming | |||
DELETING BLANK ROWS | Excel Programming |