Search : in
By :

Excel Macro for deleting rows IF cells not...

Last answer on Jan 20, 2009 6:50:30 pm GMT elmaco, on Jul 3, 2008 6:48:50 am BST 
 Report this message to moderators

Hello,

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.

/M

Configuration: Windows XP
Internet Explorer 6.0

Best answers for « Excel Macro for deleting rows IF cells not... » in :
Excel Macro Help Please (Solved) Show
Excel – Macro to detect and hide blank rows Show Excel – Macro to detect and hide blank rows Issue Solution Note Issue I want a macro to unhide about 20 blank rows copy values into the top row then hide the remaining rows (some cells have fill though) then the next time it will...
Macro for deleting row with condition Show
[VBA] Deleting a word in a range of cell Show[VBA] Deleting a word in a range of cell In the case you want to delete a word in a sentence, just create a small macro that removes the word. But it will become difficult when you have word like, for example, "Theword" or "THEWORD" or...
Colouring cells on conditions ShowColouring cells on conditions There are many pratical functions under Excel which is not commonly used. Example: If you wish a cell automatically turns red (or other formatting border, frame etc) under one condition: a result, a...
Excel – Macro for copy & paste selected range ShowExcel – Macro for copy & paste selected range Issue Solution Note Issue I have a summary of data by month in one sheet and the raw data in another sheet. Instead of creating multiple worksheets for my raw data for each month, I want...
Ask your question Reply

1

Jason, on Dec 10, 2008 6:39:54 pm GMT
  • +6

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

'Rows(J).Select



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

'Rows(J).Select



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

   
Reply to Jason

3

 jswareiv, on Jan 20, 2009 6:50:30 pm GMT
  • +16

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.

   
Reply to jswareiv

2

JB, on Jan 10, 2009 2:56:21 pm GMT
  • +8

Hi,

I have the same challenge, delete every row except for a specified value in col A. I'd like to use the approach listed for the macro, where you highlight a cell's value to keep, and delete everything else. When I pasted in the macro as shown (I don't know a thing about VBA, this is my first time needing to use it), I get the message box, but no value shown that I highlighted before running the macro. I clicked ok, then got a window, "Microsoft Visual Basic, error 400". Can you help this newbe to get the code to run? I've spend days trying to get sample code to work with no results. Thanks, Jim

   
Reply to JB
Ask your question Reply
They need your help