Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I test if a cell is empty? | Excel Discussion (Misc queries) | |||
Test if the range is empty | Excel Discussion (Misc queries) | |||
Test for an empty selection | Excel Programming | |||
Test if Clipboard is empty | Excel Programming | |||
Better way to test for empty Recordset | Excel Programming |