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 :
VBA macro to move to the cell 'underneath' ShowVBA macro to move to the cell 'underneath' Issue Solution Issue If you need need VBA macro to move to the cell 'underneath' the particular button that is being pressed, series of buttons down the page, each will run the same macro/do...
Excel tips : How to insert date in a cell ShowExcel tips : How to insert date in a cell Below are some tips on how to insert date and time in an excel cell for a specific purpose:- To insert current date, press CTRL ¯+ ;¯ in the chosen cell. To insert current time, press CTRL¯+...
Avoid duplicates in Excel ShowAvoid duplicates in Excel In order to avoid duplication when encoding in a column from an excel sheet: take the conditional format on the first cell under the headings (eg A2) choose the following formula:...
Worksheet - Cells ShowThe Concept of a Cell A "cell" is the intersection between a line (horizontal) and a column (vertical) on a worksheet. Thus, the name of the line combined with the name of the column gives the cell's coordinates (the term address is sometimes also...
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
  • +15

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
  • +7

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