CODEWITHSUNDEEP
excelexcel-2007vba
AME
AME

Reputation: 5300

Filtering Worksheet Data Using VBA

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!

Upvotes: 2

Views: 14145

Answers (1)

brettdj
brettdj

Reputation: 55672

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

Upvotes: 1

Related Questions