Test for empty row
Hi MahrYon,
How must I test for an empty row (countif?) in a macro and
subsequently delete this row?
Try something like:
'=================
Sub aTester2()
Dim rCell As Range
Dim Rng As Range
Dim delRng As Range
Dim WB As Workbook
Dim SH As Worksheet
Dim CalcMode As Long
Set WB = ActiveWorkbook '<<====== CHANGE
Set SH = WB.Sheets("Sheet3") '<<====== CHANGE
With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With
On Error Resume Next
Set Rng = SH.UsedRange.Columns(1). _
SpecialCells(xlBlanks)
On Error GoTo 0
If Not Rng Is Nothing Then
For Each rCell In Rng.Cells
If Application.CountA(rCell.EntireRow) = 0 Then
If delRng Is Nothing Then
Set delRng = rCell
Else
Set delRng = Union(rCell, delRng)
End If
End If
Next rCell
End If
If Not delRng Is Nothing Then
delRng.EntireRow.Delete
Else
'no blank rows found - do nothing
End If
With Application
.Calculation = CalcMode
.ScreenUpdating = True
End With
End Sub
'<<=================
---
Regards,
Norman
"MahrYon" wrote in message
oups.com...
Dear Excel-perts,
How must I test for an empty row (countif?) in a macro and subsequently
delete this row?
I made a worksheet which fits in an A4-format in order to print one
page.
This sheet is filled in by guest users and sometimes they write more
than one line in a cell, so its height doubles.
Is there a test to measure the height of the total sheet?
By deleting empty rows in this sheet I could try to make the hight of
this sheet fit again in the A4-format.
Thanks in advance,
MahrYon
|