Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
finding empty rows and deleting
I have range of data that varies. The data has empty rows of data. I need to find the empty rows and delete them. Need help creating VBA code to do this.
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
finding empty rows and deleting
I must have got this from Google Groups (archived NG postings, check it
out!) or Ron's web site. It deletes all blank rows in a sheet, actually any row with a blank in column 1, same thing in your case. If you have some blank rows outside your range of data that you want to keep, you'd have to tailor. Public Sub DeleteBlankRows() 'Ron De Bruin Dim column_with_blanks As Long column_with_blanks = 1 On Error Resume Next 'In case there are no blank rows Columns(column_with_blanks).SpecialCells(xlCellTyp eBlanks).EntireRow.Delete On Error GoTo 0 End Sub hth, Doug Glancy "Ryan" wrote in message ... I have range of data that varies. The data has empty rows of data. I need to find the empty rows and delete them. Need help creating VBA code to do this. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
finding empty rows and deleting
Ryan
Delete entire rows based on blanks in your selected column. Public Sub DeleteRowOnCell() ''delete any row that has a blank in selected column(s) Set coltocheck = Application.InputBox(prompt:= _ "Select A Column", Type:=8) ' On Error Resume Next coltocheck.SpecialCells(xlCellTypeBlanks).EntireRo w.Delete ActiveSheet.UsedRange End Sub Assuming entire row has to be blank. Sub DeleteEmptyRows() ''only if entire row is blank LastRow = ActiveSheet.UsedRange.Row - 1 + _ ActiveSheet.UsedRange.Rows.Count Application.ScreenUpdating = False For R = LastRow To 1 Step -1 If Application.CountA(Rows(R)) = 0 Then Rows(R).Delete Next R End Sub Gord Dibben Excel MVP On Wed, 21 Apr 2004 21:01:17 -0700, "Ryan" wrote: I have range of data that varies. The data has empty rows of data. I need to find the empty rows and delete them. Need help creating VBA code to do this. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
finding empty rows and deleting
I've found that it's faster in Excel to insert a numbered column,
then sort the data column to drive empty rows to bottom of table, then delete empty rows and rearrange the table back to the original order. This is faster than walking the table (with code), finding blank rows and deleting one at a time. The following assumes your table starts in a1, adjust address values if your column has header Sub DeleteEmptyRows() 'insert new column 'a' and number it sequentially for sorting Columns("A:A").Insert Shift:=xlToRight Range("A1").FormulaR1C1 = "1" Range("A2").FormulaR1C1 = "2" Range("A3").FormulaR1C1 = "3" Set StartRange1 = Range("a3") Set EndRange1 = Range("B65536").End(xlUp).Offset(0, -1) Range(StartRange1, EndRange1).DataSeries Rowcol:=xlColumns, Type:=xlLinear, Date:=xlDay, Step:=1, Trend:=False 'sort by column 'B' to drive empty rows to bottom of column Range("B1").Sort Key1:=Range("B1"), Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom 'delete empty rows Set StartRange2 = Range("b1").End(xlDown).Offset(1, -1) Set EndRange2 = Range("A65536").End(xlUp) Range(StartRange2, EndRange2).EntireRow.Delete 'sort again by column 'A' to restore original order Range("A1").Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom 'delete column 'A' Columns("A:A").Delete End Sub hope this helps "Ryan" wrote in message ... I have range of data that varies. The data has empty rows of data. I need to find the empty rows and delete them. Need help creating VBA code to do this. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Deleting empty rows | New Users to Excel | |||
Deleting Rows Between Empty Cells | Excel Worksheet Functions | |||
deleting empty rows | Excel Discussion (Misc queries) | |||
Deleting All Empty Rows | Excel Discussion (Misc queries) | |||
Deleting unwanted and empty rows... | Excel Discussion (Misc queries) |