[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1205
  • Last Modified:

Help with vb macro in excel to check a column in active row and delete the row.

I need a macro that will check the cell in the avctive row in a specific column agains a cell on another sheet and then if it does not fit the criteria then delete the row.  here is what I have right now.

Range("A1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Copy
    Sheets.Add
    ActiveSheet.Paste
     Range("A2").Select
      Do Until IsEmpty(ActiveCell)
        if
          activerow(b)<= activesheet.sheet1 range("g1")
        then
          Selection.EntireRow.Delete
        ActiveCell.Offset(1, 0).Select

I have never coded in vb before so I know at least part of my problem is syntax and key words thanks for your help.
0
vlahomt
Asked:
vlahomt
  • 3
1 Solution
 
patrickabCommented:
vlahomt,

You need something like this:

Sub deleter()
Dim lastrow As Long
Dim i As Long

lastrow = Sheets("Sheet1").[A65536].End(xlUp).Row

For i = lastrow To 1 Step -1
    If Sheets("Sheet1").Cells(i, 1) <= Sheets("Sheet1").[G1] Then
        Sheets("Sheet1").Rows(i).EntireRow.Delete
    End If
Next i

End Sub

Note it starts at the bottom of the data and works upwards - thus Step -1.

Hope it helps

Patrick
0
 
vlahomtAuthor Commented:
Patrick,

Can I change the scenerio a little bit.
what if I want to test multiple cells on sheet1 and if each cell meets the condition copy the eniter row to a new sheet.

so if cell A1>10 and cell C1<2 and cell F1>0 then copy row 1 to sheet 2 row 1.

then move down a row on sheet 2 to allow for the next row to copy over.
0
 
patrickabCommented:
vlahomt,

That is a totally different question to the one you started with. Perhaps you'd like to post another question for that.

Patrick
0
 
patrickabCommented:
0
 
Computer101Commented:
Forced accept.

Computer101
EE Admin
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now