Deleting blank rows in a range or another fill approach

[BigDave]

I have an area in a workbook that is 16 columns by 101 rows. With
formulas it looks for certain information in other areas of the same
sheet and fills in accordingly if the criteria are met. The data in
this array will then be copied (values only) and placed into a Data
Sheet for use with PivotTable.

If the cell in column DX is blank, then the entire row of that array is
blank. What I'd like to do is create a formula or macro that will look
to see if the cells in DX column and if it finds a blank cell, the
corresponding row is deleted so I don't get this huge array with a
whole bunch of blanks.

Or...

Is there another approach I can take with filling out this array?
Currently I'm using a IF true/false statement to seach for information
in a certain cell and fill in the other information accordingly. If
nothing is in the key cell, the row is blank. Is there a conditional
or "stepping" type of seach I can use to do this same thing?

 

[jmagdziarz]

Here's one option:

Sub DeleteBlankRows()

Dim rownum As Integer
rownum = 1

Do Until rownum = 101

cellloc = "DX" & rownum

Range(cellloc).Select
If Range(cellloc).Value = "" Then
Rows(rownum).Select
Selection.Delete Shift:=xlUp
Else
End If

rownum = rownum + 1
Loop

Range("A1").Select

End Sub

 

[Tom Ogilvy]

If the cells in DX have hard coded values (not formulas), then:

Dim rng as Range, rng1 as Range
set rng = Range("DX1X101").SpecialCells(xlConstants)
set rng1 = intersect(Range(A1).Resize(1,16).EntireColumn, _
rng.EntireRow)
rng1.copy Destination:=Worksheets("Sheet2").Range("A1")

 

[Ron de Bruin]

Hi BigDave

One way with a loop for row 1-101 on the activesheet

Sub Example2()
Dim Lrow As Long
Dim CalcMode As Long
Dim StartRow As Long
Dim EndRow As Long
With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

With ActiveSheet
.DisplayPageBreaks = False
StartRow = 1
EndRow = 101
For Lrow = EndRow To StartRow Step -1
If IsError(.Cells(Lrow, "DX").Value) Then
'Do nothing, This avoid a error if there is a error in the cell

ElseIf IsEmpty(.Cells(Lrow, "DX").Value) Then .Rows(Lrow).Delete
'This will delete the row if the cell is empty

End If
Next
End With
With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With
End Sub

 

[BigDave]

Alright - I've moved the formulas to a second sheet (that I'll later
hide) so that everything is nice and neat, starting in column A. The
formulas are only in cells A2:A21 for testing.

Dim Lrow As Long
Dim CalcMode As Long
Dim StartRow As Long
Dim EndRow As Long
With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

With ActiveSheet
.DisplayPageBreaks = False
StartRow = 2
EndRow = 21
For Lrow = EndRow To StartRow Step -1
If IsError(.Cells(Lrow, "a").Value) Then
'Do nothing, This avoid a error if there is a error in the cell

ElseIf IsText(.Cells(Lrow, "a").Value) Then .Rows(Lrow).Delete
'This will delete the row if the cell is empty

End If
Next
End With
With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With


End Sub

Column A is filled with these formulas - *=IF(F2=" ","",Sheet2!$D$4)*.
When I run the code, I get nothing. Ideas?

Here is the other code -

Sub DeleteBlankRows()

End Sub
Dim rownum As Integer
rownum = 2

Do Until rownum = 21

cellloc = "A" & 2

Range(cellloc).Select
If Range(cellloc).Value = "" Then
Rows(rownum).Select
Selection.Delete Shift:=xlUp
Else
End If

rownum = rownum + 1
Loop

Range("A1").Select

End Sub

When I ran this code, it delted the columns between the first and next
cells with text, but none of the others (that contain forumlas). ???

Thanks

 

[Ron de Bruin]

Use

ElseIf .Cells(Lrow, "A").Value = "" Then .Rows(Lrow).Delete