Test for empty row
 

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

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

Test for empty row
 

You can't use the Fit to Page option?

--
Regards,
Tom Ogilvy


"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