Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete empty rows.
I am looking for a macro to auto-delete rows that are empty between columns
A and N. The main column I am concerned with is B. If B? is empty, delete the entire row. Thanks for your time and help, Steve |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete empty rows.
Hi Steve,
Try: '======================= Public Sub Tester02() On Error Resume Next Columns("B:B").SpecialCells(xlBlanks). _ EntireRow.Delete On Error GoTo 0 End Sub '<<======================= Alternatively, look at using the AutoFilter featutre. --- Regards, Norman "Steve" wrote in message ... I am looking for a macro to auto-delete rows that are empty between columns A and N. The main column I am concerned with is B. If B? is empty, delete the entire row. Thanks for your time and help, Steve |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete empty rows.
Hi Steve
Try this for the activesheet usedrange Sub Example1() Dim Firstrow As Long Dim Lastrow As Long Dim Lrow As Long Dim CalcMode As Long With Application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False End With Firstrow = ActiveSheet.UsedRange.Cells(1).Row Lastrow = ActiveSheet.UsedRange.Rows.Count + Firstrow - 1 With ActiveSheet .DisplayPageBreaks = False For Lrow = Lastrow To Firstrow Step -1 If Application.CountA(.Range(.Cells(Lrow, "A"), .Cells(Lrow, "N"))) = 0 Then .Rows(Lrow).Delete Next End With With Application .ScreenUpdating = True .Calculation = CalcMode End With End Sub More info here http://www.rondebruin.nl/delete.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Steve" wrote in message ... I am looking for a macro to auto-delete rows that are empty between columns A and N. The main column I am concerned with is B. If B? is empty, delete the entire row. Thanks for your time and help, Steve |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete empty rows.
iLastRow = cells(Rows.Count,"B").End(xlUp).Row For i = iLastRow to 1 Step -1 If Cells(i,"B").Value = "" Then Rows(i).Delete End If Next i or iLastRow = Cells(Rows.Count, "B").End(xlUp).Row Range("B1:B" & iLastRow).SpecialCells(xlCellTypeBlanks).EntireRow .delete -- HTH Bob Phillips "Steve" wrote in message ... I am looking for a macro to auto-delete rows that are empty between columns A and N. The main column I am concerned with is B. If B? is empty, delete the entire row. Thanks for your time and help, Steve |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete empty rows.
This works great, however, is there a way to limit it to a range instead of
applying it to the entire wksht. Steve "Norman Jones" wrote: Hi Steve, Try: '======================= Public Sub Tester02() On Error Resume Next Columns("B:B").SpecialCells(xlBlanks). _ EntireRow.Delete On Error GoTo 0 End Sub '<<======================= Alternatively, look at using the AutoFilter featutre. --- Regards, Norman "Steve" wrote in message ... I am looking for a macro to auto-delete rows that are empty between columns A and N. The main column I am concerned with is B. If B? is empty, delete the entire row. Thanks for your time and help, Steve |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete empty rows.
Hi Steve Try,
'======================= Public Sub Tester02() Dim Rng As Range, Rng1 As Range Set Rng = Range("A1:K30") '<<====== CHANGE to suit On Error Resume Next Set Rng1 = Intersect(Rng, _ Columns("B:B").SpecialCells(xlBlanks)) On Error GoTo 0 If Not Rng1 Is Nothing Then Rng1.EntireRow.Delete End Sub '<<======================= Change address of Rng to limit the rows to be deleted. --- Regards, Norman "Steve" wrote in message ... This works great, however, is there a way to limit it to a range instead of applying it to the entire wksht. Steve "Norman Jones" wrote: Hi Steve, Try: '======================= Public Sub Tester02() On Error Resume Next Columns("B:B").SpecialCells(xlBlanks). _ EntireRow.Delete On Error GoTo 0 End Sub '<<======================= Alternatively, look at using the AutoFilter featutre. --- Regards, Norman "Steve" wrote in message ... I am looking for a macro to auto-delete rows that are empty between columns A and N. The main column I am concerned with is B. If B? is empty, delete the entire row. Thanks for your time and help, Steve |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete empty rows.
Worked like a charm. Thanks.
"Norman Jones" wrote: Hi Steve Try, '======================= Public Sub Tester02() Dim Rng As Range, Rng1 As Range Set Rng = Range("A1:K30") '<<====== CHANGE to suit On Error Resume Next Set Rng1 = Intersect(Rng, _ Columns("B:B").SpecialCells(xlBlanks)) On Error GoTo 0 If Not Rng1 Is Nothing Then Rng1.EntireRow.Delete End Sub '<<======================= Change address of Rng to limit the rows to be deleted. --- Regards, Norman "Steve" wrote in message ... This works great, however, is there a way to limit it to a range instead of applying it to the entire wksht. Steve "Norman Jones" wrote: Hi Steve, Try: '======================= Public Sub Tester02() On Error Resume Next Columns("B:B").SpecialCells(xlBlanks). _ EntireRow.Delete On Error GoTo 0 End Sub '<<======================= Alternatively, look at using the AutoFilter featutre. --- Regards, Norman "Steve" wrote in message ... I am looking for a macro to auto-delete rows that are empty between columns A and N. The main column I am concerned with is B. If B? is empty, delete the entire row. Thanks for your time and help, Steve |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Hpw do I delete multiple empty rows found between filled rows? | Excel Worksheet Functions | |||
How to Delete empty rows in excel in b/w rows with values | Excel Worksheet Functions | |||
Cut filtered rows, paste into next empty row of new sheet, and delete cut rows | Excel Worksheet Functions | |||
delete empty rows | Excel Programming | |||
Delete rows if they are empty | Excel Programming |