Prev: how to append and remove dups
Next: Separating information in a text string
From: JC on 18 Jul 2008 15:05
Hi,

I appreciate your help.
I need to run a conditional If Then statement on a defined table. If the
cell in column 1 is blank or null, then delete the entire row, and run this
statement on every row until the end of the table.

Thanks again!,
JC
From: Per Jessen on 18 Jul 2008 15:31
Hi JC

Try this, just change TargetRange as desired.

Sub DeleteEmptyRow()
Dim TargetRange As Range
Set TargetRange = Range("A10:A20") '<==Change to suit

EndRow = TargetRange.Cells(1, 1).Row
StartRow = TargetRange.Rows.Count + EndRow - 1

For r = StartRow To EndRow Step -1
If Cells(r, 1).Value = "" Or Cells(r, 1).Value = 0 Then
Rows(r).Delete
End If
Next
End Sub

Regards,
Per

"JC" <JC(a)discussions.microsoft.com> skrev i meddelelsen
news:94367524-0C32-4197-85EC-D67EB167EFCC(a)microsoft.com...
> Hi,
>
> I appreciate your help.
> I need to run a conditional If Then statement on a defined table. If the
> cell in column 1 is blank or null, then delete the entire row, and run
> this
> statement on every row until the end of the table.
>
> Thanks again!,
> JC

From: JC on 18 Jul 2008 16:24
Thanks Per,

I actually decided to select the range of the first column, then use the Go
To, Special, Blanks and then Delete entire rows.

I appreciate your help. Here is my code. I comment out a lot, but I am a
novice at this. Thx!

Sub Delete_Blank_Rows()
'
' Delete Blank Object Code Rows, Selects Expenditures sheet
'
Sheets("Expenditures").Select
'
' Delete Blank Object Code Rows on Budget Entries
'
Range("TblBudgetExp[[#All],[Obj]]").Select
Range("B268").Activate
Selection.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
'
' Delete Blank Rows on Transfers
'
Range("TblTransfersExp[[#All],[Obj]]").Select
Range("B259").Activate
Selection.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
'
' Delete Blank Rows on Actuals
'
Range("TblActualsExp[[#All],[Obj ]]").Select
Range("B224").Activate
Selection.SpecialCells(xlCellTypeBlanks).EntireRow.Delete

'
' Selects Revenue Sheet
'

Sheets("Revenue").Select
'
' Delete Blank Rows on Revenue Budget Entries
'

Range("TblBudgetRev[[#All],[Obj]]").Select
Range("B21").Activate
Selection.SpecialCells(xlCellTypeBlanks).EntireRow.Delete

End Sub

I hope I don't comment out on this, but I'm a novice and I try to sleep
between these sessions.

Thx!
JC












JC

"Per Jessen" wrote:

> Hi JC
>
> Try this, just change TargetRange as desired.
>
> Sub DeleteEmptyRow()
> Dim TargetRange As Range
> Set TargetRange = Range("A10:A20") '<==Change to suit
>
> EndRow = TargetRange.Cells(1, 1).Row
> StartRow = TargetRange.Rows.Count + EndRow - 1
>
> For r = StartRow To EndRow Step -1
> If Cells(r, 1).Value = "" Or Cells(r, 1).Value = 0 Then
> Rows(r).Delete
> End If
> Next
> End Sub
>
> Regards,
> Per
>
> "JC" <JC(a)discussions.microsoft.com> skrev i meddelelsen
> news:94367524-0C32-4197-85EC-D67EB167EFCC(a)microsoft.com...
> > Hi,
> >
> > I appreciate your help.
> > I need to run a conditional If Then statement on a defined table. If the
> > cell in column 1 is blank or null, then delete the entire row, and run
> > this
> > statement on every row until the end of the table.
> >
> > Thanks again!,
> > JC
>
>
 | 
Pages: 1
Prev: how to append and remove dups
Next: Separating information in a text string