[Excel] Macro to delete

[nathanpiggy]

Hey
Im currently in the process of editing a huge spreadsheet, step by step, and its proving to be a huge task.

I was wondering if there is a macro out there, or if anyone can help with one, which will delete an entire row (not just remove its contents and leave it blank, delete the row and shift it up), if the cell on that row in column "G" is empty.

The idea basically would be the code would work through every line of the spreadsheet, deleting any rows with a blank cell in the "G" column.

so i guess it would include "Selection.Delete Shift:=xlUp" somewhere in the code...?

any help would be apreciated?

thanks

[RobDog888]

Welcome to the Forums.

Yes, a macro recording will give you the starting point of how Excel would do part of it.

You will want to inclide a few routines like...

Get the row count so you are not going through the entire sheel.
Test for G? cell if its blank.
Delete the row if it is blank.

Dim i As Long
Dim lRowCount As Long
lRowCount = Workbooks(1).Sheets("Sheet1").Cells.SpecialCells(xlCellTypeLastCell).Row
For i = 1 To lRowCount
    If Workbooks(1).Sheets("Sheet1").Cells(i, 7).Value = vbNullString Then
        Workbooks(1).Sheets("Sheet1").Rows(i & ":" & i).Delete Shift:=xlUp
    End If
Next

[nathanpiggy]

thanks for the quick reply, especially with how early in the morning it is over there

Ive put this into a macro, however it seems to give me
"run time error 9, subscript out of range" error, when i debug it highlights "lRowCount = Workbooks(1).Sheets("Sheet1").Cells.SpecialCells(xlCellTypeLastCell).Row" line

There are 7590 rows in the spreadsheet im editing

Im do know a bit of vb code, however this is in excel seems to be more confusing

any further advice?

thanks again

[nathanpiggy]

no matter, i figured it out, the sheet had been renamed, i just had to change the name

all sorted then, thanks =]