Excel Macro for deleting rows IF cells not...

elmaco - Jul 3, 2008 at 01:48 AM
 Jo - Oct 12, 2011 at 06:38 AM

I need help to create a Excel macro.
Basicly what I have is a sheet full with information about different departments and what I want to do is delete every row EXCEPT the rows that contain some specified values (wich I would like to enter on running the script).

Lets say in the column that names the department (in my sheet named "Avd"), I would want the script to look for any cell that does not contain, for example, the numbers 1, 3, 5, 6 or 21... and so on (i have about 36 different numbers).

Is there any good way to do this?

Thanks very much for your time.


3 responses

I spent a lot of time trying to figure this out and finally got the correct Macro. All you have to do is highlight the information in the column and then run the following Macro. There will be a box that will prompt you what vaule you want to keep. This is good for up to 30,000 rows.

Sub DeleteRows()

Dim strToDelete As String

Dim rngSrc As Range

Dim NumRows As Integer

Dim ThisRow As Integer

Dim ThatRow As Integer

Dim ThisCol As Integer

Dim J As Integer

Dim DeletedRows As Integer

strToDelete = InputBox("Value to Trigger Keep, Jason????", "Delete Rows")

Set rngSrc = ActiveSheet.Range(ActiveWindow.Selection.Address)

NumRows = rngSrc.Rows.Count

ThisRow = rngSrc.Row

ThatRow = ThisRow + NumRows - 1

ThisCol = rngSrc.Column

Dim topRows As Integer

Dim bottomRows As Integer

bottomRows = 30000

For J = ThisRow To NumRows Step 1

If Cells(J, ThisCol) = strToDelete Then


topRows = J

Exit For

DeletedRows = DeletedRows + 1

End If

Next J

For J = (topRows + 1) To NumRows Step 1

If Cells(J, ThisCol) <> strToDelete Then


bottomRows = J

Exit For

'DeletedRows = DeletedRows + 1

End If

Next J

If topRows <> 4 Then

ActiveSheet.Range(Cells(4, 1), Cells(topRows - 1, 52)).Select

Selection.delete Shift:=xlUp

End If

ActiveSheet.Range(Cells(bottomRows - topRows + 4, 1), Cells(30000, 52)).Select

Selection.delete Shift:=xlUp

'MsgBox "Number of deleted rows: " & DeletedRows

End Sub
I have a sort of similar issue, but easier. I have a worksheet that references other worksheets in the workbook. This is a quoting tool I use and so on the configure worksheet, I will select those items I need and the Proposal worksheet has IF statements for all products. Obviously I don't use all products, so I am left with a spread sheet with lots of blank rows. I would like to design a macro that will delete blank rows if the quantity (column N) is blank and to check from row 18-53. Can you help me with that? I am not a programmer and don't know VB, but would like to use the macro instead of having to go back and delete the blank rows every time. Thanks.
Are you still interested in solution? I needed it also so I've wrote a code which handle it.
Let me know,