Loop to select rows based on cell value

Question

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?

Could we conclude that the data has alternating areas with zeros and areas of interest and you want to copy the areas of interest to various destinations? If so, could you explain where those areas of interest need to be copied to? Also, is your data set in table format (1st row are headers and no empty rows or columns)? In which cell does the data start? Add more detail, possibly some more or less related code to make things easier (possible). Add it to your post. — VBasic2008
– VBasic2008, Commented Mar 1, 2022 at 11:49

VBasic2008 · Accepted Answer · 2023-02-01 21:06:26Z

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

Collectives™ on Stack Overflow

Loop to select rows based on cell value

1 Answer 1

Retrieve Non-Zero Areas

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Retrieve Non-Zero Areas

Comments

Your Answer

Sign up or log in

Post as a guest

Related