Deleting rows on a selected parameter - new person!

[slay0r]

Hi Guys,

I'm entirely new to the world of macro's and VBA's. I know generally how it all functions but now I want to get into it a little more!

I'm trying to delete rows that have a selected parameter. I've got a lot of data but I want to delete anything less than 500 pounds. I've got both debits and credits so that will have to be taken into account. How would I go about setting that up as an automated process?

Thanks in advance for all the help

Jonathan

EDIT: While I'm on here, is it possible to do a macro that will vlookup to a certain range? All I need is a simple yes or no answer to that one!

 

[Sektor]

[COLOR="Blue"]Sub[/COLOR] Delete500Pounds()
    [COLOR="Blue"]Dim[/COLOR] cell [COLOR="Blue"]As[/COLOR] Range
    [COLOR="Blue"]For[/COLOR] [COLOR="Blue"]Each[/COLOR] cell [COLOR="Blue"]In[/COLOR] ActiveSheet.UsedRange
        [COLOR="Blue"]If[/COLOR] cell < 500 [COLOR="Blue"]Then[/COLOR] cell.ClearContents
    [COLOR="Blue"]Next[/COLOR]
[COLOR="Blue"]End[/COLOR] [COLOR="Blue"]Sub[/COLOR]

 

[slay0r]

Can you explain that a little for me?

What do I need to input and where to make this run? I need it to delete the entire row if there's less than 500 pounds in the AG column, the range is 1-60000 so how do I need to put this into the code below? I'm a complete noob on these things so apologies for being extremely slow and thank you for your help!

 

[mirabeau]

Hi slay0r,

To run a VBA code, first press ALT F11.

This should open the VBA code window.

Then from the menu at the top click on Insert -> module.

Copy and paste the code of your choice (mine is below, it should handle 60,000 rows OK without much waiting) into the module.

Then, making sure you have your relevant worksheet active, put your cursor somewhere in the pasted code in the module and press F5.

This should run the code which will hopefully do the things you want to your worksheet.

But make sure you run any code on test data first to be sure that it works OK

Sub delrows()
Dim col As Range, c As Range, nr&
Dim u(), k&, e, p&
Set col = Range("AG:AG")
Set c = Cells(1, Columns.Count)
nr = col(Rows.Count).End(3).Row
ReDim u(1 To nr, 1 To 1)
For Each e In col.Resize(nr).Value
    k = k + 1
    If e < 500 Then u(k, 1) = 1: p = p + 1
Next
c.Resize(nr) = u
Cells(1, 1).Resize(nr, Columns.Count).Sort c, 1
Cells(1, 1).Resize(p, Columns.Count).Delete
End Sub

Your request "EDIT: While I'm on here, is it possible to do a macro that will vlookup to a certain range? All I need is a simple yes or no answer to that one!". Generally the answer would be YES, but more details would be useful.

 

[slay0r]

Hi Guys,

Previously I wanted to delete anything less than £500. This time I want to do the exact same thing but I only want to delete the zero's.

It's in column AL, I just literally need it to delete the rows with the value 0 in the column AL. Is there an easy way to do this?