Deleting blank rows in a range or another fill approach

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?

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

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

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


--
Regards,
Tom Ogilvy


"BigDave" <[email protected]> wrote in
message news:[email protected]...
>
> 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?
>
>
> --
> BigDave
> ------------------------------------------------------------------------
> BigDave's Profile:
http://www.excelforum.com/member.php...fo&userid=7741
> View this thread: http://www.excelforum.com/showthread...hreadid=376074
>

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


--
Regards Ron de Bruin
http://www.rondebruin.nl



"BigDave" <[email protected]> wrote in message
news:[email protected]...
>
> 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?
>
>
> --
> BigDave
> ------------------------------------------------------------------------
> BigDave's Profile: http://www.excelforum.com/member.php...fo&userid=7741
> View this thread: http://www.excelforum.com/showthread...hreadid=376074
>

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

Use

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


--
Regards Ron de Bruin
http://www.rondebruin.nl



"BigDave" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>
>
> --
> BigDave
> ------------------------------------------------------------------------
> BigDave's Profile: http://www.excelforum.com/member.php...fo&userid=7741
> View this thread: http://www.excelforum.com/showthread...hreadid=376074
>