delete rows when cell is blank

**mscanlo** · 04-30-2007, 04:12 PM

i have the following code:

Sub XXXXX()



' DeleteThreeRows Macro
' Macro recorded 4/23/2007 by Michael Scanlon

    Columns("A:C").Select
    Range("C1").Activate
    Selection.Delete Shift:=xlToLeft

' AddDateAndCalculateSalary Macro
' Macro recorded 4/26/2007 by mscanlon

    ActiveWindow.SmallScroll Down:=-6
    Range("BB1").Select
    ActiveCell.FormulaR1C1 = "Date"
    Range("BC1").Select
    ActiveCell.FormulaR1C1 = "Salary"
    Range("BB2").Select
    ActiveCell.FormulaR1C1 = "=TODAY()"
    Range("BB2").Select
    Selection.AutoFill Destination:=Range("BB2:BB500"), Type:=xlFillDefault
    Range("BB2:BB500").Select
    
    Range("BC2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(RC[-1]<RC[-45],RC[-44],IF(RC[-1]<RC[-41],RC[-40],IF(RC[-1]<RC[-37],RC[-36],IF(RC[-1]<RC[-33],RC[-32],IF(RC[-1]<RC[-29],RC[-28],IF(RC[-1]<RC[-25],RC[-24],""No Salary Available""))))))"
    Range("BC2").Select
    Selection.AutoFill Destination:=Range("BC2:BC500"), Type:=xlFillDefault
    Range("BC2:BC500").Select

'
' SalaryInput Macro
' Macro recorded 4/26/2007 by mscanlon

    Range("BD1").Select
    ActiveCell.FormulaR1C1 = "Salary Input"
    Range("BD2").Select
    ActiveCell.FormulaR1C1 = "=IF(RC[-1]=0,""Contract Expired"",RC[-1])"
    Range("BD2").Select
    
    Selection.AutoFill Destination:=Range("BD2:BD500"), Type:=xlFillDefault
    Range("BD2:BD500").Select

' TitleInput Macro
' Macro recorded 4/26/2007 by mscanlon

    Range("BE1").Select
    ActiveCell.FormulaR1C1 = "Title Input"
    Range("BE2").Select
    ActiveCell.FormulaR1C1 = "=IF(RC[-50]="""",RC[-49],RC[-50])"
    Range("BE2").Select
    Selection.AutoFill Destination:=Range("BE2:BE500"), Type:=xlFillDefault
    Range("BE2:BE500").Select
    ActiveWindow.SmallScroll Down:=-264

' NoticeInput Macro
' Macro recorded 4/27/2007 by mscanlon

    Range("BF2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(RC[-4]<RC[-48],RC[-49],IF(RC[-4]<RC[-44],RC[-45],IF(RC[-4]<RC[-40],RC[-41],IF(RC[-4]<RC[-36],RC[-37],IF(RC[-4]<RC[-32],RC[-33],IF(RC[-4]<RC[-28],RC[-29],""Expired""))))))"
    Range("BF2").Select
    Selection.AutoFill Destination:=Range("BF2:BF500"), Type:=xlFillDefault
    Range("BF2:BF500").Select

' bg Macro
' Macro recorded 4/27/2007 by mscanlon
'
    Range("BG2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(RIGHT(RC[-26],4)=""YEAR"",LEFT(RC[-26],1)*52,IF(RIGHT(RC[-26],5)=""WEEKS"",LEFT(RC[-26],3),IF(RC[-26]=""NONE"",""NONE"",IF(RIGHT(RC[-26],6)=""MONTHS"",LEFT(RC[-26],3)*4,IF(RC[-26]=""SEE NOTES"",""SEE NOTES"",""Error"")))))"
    Range("BG2").Select
    Selection.AutoFill Destination:=Range("BG2:BG500"), Type:=xlFillDefault
    Range("BG2:BG500").Select

' BH Macro
' Macro recorded 4/27/2007 by mscanlon
'

'
    Range("BH2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(RIGHT(RC[-1],1)="""",CONCATENATE(RC[-1],""WEEKS""),IF(RC[-1]=""SEE NOTES"",""SEE NOTES"",IF(RC[-1]=""none"",""NONE"",IF(RIGHT(RC[-1],1)>0,CONCATENATE(RC[-1],"" "",""WEEKS""),""Error""))))"
    Range("BH2").Select
    Selection.AutoFill Destination:=Range("BH2:BH500"), Type:=xlFillDefault
    Range("BH2:BH500").Select

'
' BI Macro
' Macro recorded 4/27/2007 by mscanlon
'

'
    Range("BI2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(RC[-2]=""SEE NOTES"",RC[-2],IF(RC[-2]=""NONE"",""NONE"",IF(RIGHT(RC[-28],6)=""MONTHS"",DATE(YEAR(RC[-3]),MONTH(RC[-3])+LEFT(RC[-28],3),DAY(RC[-3])),RC[-3]+RC[-2]*7)))"
    Range("BI2").Select
    Selection.AutoFill Destination:=Range("BI2:BI500"), Type:=xlFillDefault
    Range("BI2:BI500").Select

'
' BJ Macro
' Macro recorded 4/27/2007 by mscanlon
'

'
    Range("BJ1").Select
    ActiveCell.FormulaR1C1 = "Notice Input"
    Range("BJ2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(RC[-1]=""none"",""NONE"",IF(RC[-1]=""SEE NOTES"",""SEE NOTES"",IF(RC[-58]-RC[-1]<=0,""NONE"",RC[-1])))"
    Range("BJ2").Select
    Selection.AutoFill Destination:=Range("BJ2:BJ500"), Type:=xlFillDefault
    Range("BJ2:BJ500").Select

'
' BK Macro
' Macro recorded 4/27/2007 by mscanlon
'

'
    Range("BK1").Select
    ActiveCell.FormulaR1C1 = "Contract Length Input"
    Range("BK2").Select
    ActiveCell.FormulaR1C1 = "=(RC[-59]-RC[-60])/365.25"
    Range("BK2").Select
    Selection.AutoFill Destination:=Range("BK2:BK500"), Type:=xlFillDefault
    Range("BK2:BK500").Select

'
' BL Macro
' Macro recorded 4/27/2007 by mscanlon
'

'
    Range("BL1").Select
    ActiveCell.FormulaR1C1 = "Ending Salary Input"
    Range("BL2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(RC[-60]=RC[-54],RC[-53],IF(RC[-60]=RC[-50],RC[-49],IF(RC[-60]=RC[-46],RC[-45],IF(RC[-60]=RC[-42],RC[-41],IF(RC[-60]=RC[-38],RC[-37],IF(RC[-60]=RC[-34],RC[-33],""Error""))))))"
    Range("BL2").Select
    Selection.AutoFill Destination:=Range("BL2:BL500"), Type:=xlFillDefault
    Range("BL2:BL500").Select
    ActiveWindow.SmallScroll Down:=-60

'
' BL Macro
' Macro recorded 4/30/2007 by mscanlon
'

'
    Range("Bm1").Select
    ActiveCell.FormulaR1C1 = "Anchor"
    Range("Bm2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(ISERROR(SEARCH(""anchor"",RC[-8],1)),0,SEARCH(""anchor"",RC[-8],1))"
    Range("Bm2").Select
    Selection.AutoFill Destination:=Range("Bm2:Bm442"), Type:=xlFillDefault
    Range("Bm2:Bm442").Select
'
' Macro recorded 4/30/2007 by mscanlon
'

'
    Range("Bn1").Select
    ActiveCell.FormulaR1C1 = "Host"
    Range("Bn2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(ISERROR(SEARCH(""host"",RC[-9],1)),0,SEARCH(""host"",RC[-9],1))"
    Range("Bn2").Select
    Selection.AutoFill Destination:=Range("Bn2:Bn442"), Type:=xlFillDefault
    Range("Bn2:Bn442").Select

' Macro recorded 4/30/2007 by mscanlon
'

'
    Range("Bo1").Select
    ActiveCell.FormulaR1C1 = "Correspondent"
    Range("Bo2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(ISERROR(SEARCH(""correspondent"",RC[-10],1)),0,SEARCH(""correspondent"",RC[-10],1))"
    Range("Bo2").Select
    Selection.AutoFill Destination:=Range("Bo2:Bo442"), Type:=xlFillDefault
    Range("Bo2:Bo442").Select

'
' CAGR Macro
' Macro recorded 4/30/2007 by mscanlon
'

'
    Range("BR1").Select
    ActiveCell.FormulaR1C1 = "CAGR"
    Range("BR2").Select
    ActiveCell.FormulaR1C1 = "=((RC[-6]/RC[-59])^(1/RC[-7]))-1"
    Range("BR2").Select
    Selection.AutoFill Destination:=Range("BR2:BR500"), Type:=xlFillDefault
    Range("BR2:BR500").Select

'
' SigningBonusInput Macro
' Macro recorded 4/30/2007 by mscanlon
'

'
    Range("BS1").Select
    ActiveCell.FormulaR1C1 = "Signing Bonus Input"
    Range("BS2").Select
    ActiveCell.FormulaR1C1 = "=IF(RC[-20]=0,0,IF(RC[-17]<RC[-61],RC[-20],0))"
    Range("BS2").Select
    Selection.AutoFill Destination:=Range("BS2:BS500"), Type:=xlFillDefault
    Range("BS2:BS500").Select

'
' CarAllowanceInput Macro
' Macro recorded 4/30/2007 by mscanlon
'

'
    Range("BU1").Select
    ActiveCell.FormulaR1C1 = "Car Allowance Input"
    Range("BU2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(RC[-31]=""monthly"",RC[-32]*12,IF(RC[-31]=""weekly"",RC[-32]*52,IF(RC[-31]=""yearly"",RC[-32],0)))"
    Range("BU2").Select
    Selection.AutoFill Destination:=Range("BU2:BU500"), Type:=xlFillDefault
    Range("BU2:BU500").Select

' ClothingAllowance Macro
' Macro recorded 4/30/2007 by mscanlon
'

'
    Range("BT1").Select
    ActiveCell.FormulaR1C1 = "Clothing Allowance Input"
    Range("BT2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(RC[-24]=""ONE-TIME"",IF(RC[-18]<RC[-62],RC[-25],0),IF(RC[-24]=""yearly"",RC[-25],IF(RC[-24]=""default"",0,IF(RC[-24]=""term"",RC[-25],IF(LEFT(RC[-24],3)=""see"",""SEE NOTES"",""Error"")))))"
    Range("BT2").Select
    Selection.AutoFill Destination:=Range("BT2:BT500"), Type:=xlFillDefault
    Range("BT2:BT500").Select


' DeleteIncorrectTitle Macro
' Macro recorded 4/30/2007 by mscanlon
'

 Range("BW1").Select
    ActiveCell.FormulaR1C1 = "Notice Input"
    Range("BW2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(RC[-41]="""",""NONE"",IF(RC[-41]=""Default"",""NONE"",RC[-41]))"
    Range("BW2").Select
    Selection.AutoFill Destination:=Range("BW2:BW500"), Type:=xlFillDefault
    Range("BW2:BW500").Select
'
    Range("Bp1").Select
    ActiveCell.FormulaR1C1 = "DeleteIncorrectTitle"
    Range("Bp2").Select
    ActiveCell.FormulaR1C1 = "=IF(RC[-3]+RC[-2]+RC[-1]>0,1,""Delete"")"
    Range("Bp2").Select
    Selection.AutoFill Destination:=Range("Bp2:Bp442"), Type:=xlFillDefault
    Range("Bp2:Bp442").Select




Dim theRange As Range
Dim lastRow&, firstRow&, x&
Set theRange = ActiveSheet.UsedRange
Application.ScreenUpdating = False
lastRow = theRange.Cells(theRange.Cells.Count).Row
firstRow = theRange.Cells(1).Row
For x = lastRow To firstRow Step -1
If Cells(x, 68) = "Delete" Then
Rows(x).Delete
End If
Next

Columns("A:A").Select
    Selection.Insert Shift:=xlToRight
    Range("A2").Select
    ActiveCell.FormulaR1C1 = "=CONCATENATE(RC[1],"","","" "",RC[2])"
    Range("A2").Select
    Selection.AutoFill Destination:=Range("A2:A305"), Type:=xlFillDefault
    Range("A2:A305").Select


End Sub

its kind of long, you may just want to look at the bottom half. i am having trouble deleting a row if the cell in column d is blank (i think cause i already have a delete macro in the code). can anyone help?

**bhofsetz** · 04-30-2007, 06:06 PM

Highlight the portion of the code you are having trouble with.
I don't see anywhere in the code where you are checking the contents of column D to determine if that row needs to be deleted.

What error message are you getting when you say you are having trouble deleting the row?

delete rows when cell is blank

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

delete rows when cell is blank

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions