CODEWITHSUNDEEP
excelvbaloopsautofilter
Tom
Tom

Reputation: 51

Loop to select rows based on cell value

I have data from a machine which I import into Excel and it gives a dataset which is around seven columns wide and 66000 rows deep. (Sometimes more, sometimes less.)

I want to select data based on Column E and then that will select all the rows so far, I can mirror that across to another sheet.

Column E contains a couple of thousand rows of zeros, then it changes to a number greater than zero for a few thousand rows. Then it loops back to zero etc. It will do this cycle around 25 times per data set. (Always different between number of rows per loop etc.)

I think I want two loops.

Loop 1:
Look at column E if it equals zero then select the row.
Do this until Column E does not equal zero.
Mirror this selection (I can use the Range.Resize method) into different area.
Delete the selection.
Shift cells up.

I would then call a similar loop for the values greater than zero and then recall loop 1 again and do this 25 times (but that doesn't matter here).

How do I create a loop to select cells/rows of data based on the value of a cell/column?
The loop should end once it has selected the cells because each group will be moved to a different area on the workbook.

Or is there something better than loops?

Upvotes: 1

Views: 912

Answers (1)

VBasic2008
VBasic2008

Reputation: 54807

Retrieve Non-Zero Areas

Option Explicit

Sub RetrieveNonZeroAreas()
    
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    
    Dim sws As Worksheet: Set sws = wb.Worksheets("Sheet1")
    If sws.AutoFilterMode Then sws.AutoFilterMode = False ' turn off AutoFilter
    
    Dim srg As Range: Set srg = sws.Range("A1").CurrentRegion
    Dim sdrg As Range: Set sdrg = srg.Resize(srg.Rows.Count - 1).Offset(1)
    
    srg.AutoFilter 5, "<>0"
    
    Dim svdrg As Range
    On Error Resume Next
        Set svdrg = sdrg.SpecialCells(xlCellTypeVisible)
    On Error GoTo 0
    sws.AutoFilterMode = False
    
    If svdrg Is Nothing Then Exit Sub
    
    Dim arg As Range
    For Each arg In svdrg.Areas
        ' Now do what you need to do with each of the 25 or so datasets, e.g.:
        
        Debug.Print arg.Address
        
    Next arg
    
    MsgBox "Data... done.", vbInformation
    
End Sub

Upvotes: 1

Related Questions