Filtering Worksheet Data Using VBA

Question

Using: Excel 2007.

Problem: A spreadsheet contains many columns and rows of data. One column, called "Probability", contains percentage values between 0% and 100%. How does one write a macro that conditionally removes a row giving it has a "Probability" value is less than 50%?

Update: This cannot simply be done by recording a macro, as the percentage values will vary with each new data upload.

Thanks!

Solution

If for example your Probability data was in column A of the active sheet, you could use Autofilter

1. Manually, Autofilter the column, add a criteria of <50%, then delete the results

2. Run the same approach in code as below

   Sub QuickCull()
       Application.ScreenUpdating = False
       With ActiveSheet
           .AutoFilterMode = False
           .Columns("A").AutoFilter Field:=1, Criteria1:="<50%"
           .AutoFilter.Range.Offset(1, 0).EntireRow.Delete
           .AutoFilterMode = False
       End With
       Application.ScreenUpdating = True
   End Sub