Selecting rows based on range of values in cell (1 Viewer)

tmyers

Well-known member
Local time
Today, 03:25
Joined
Sep 8, 2020
Messages
1,090
It has been a minute since I have posted (had to take a break from all the dev stuff before my poor brain fried), but I have once again been handed a project to make everyone's life a little easier.

I am trying to write a macro that will look through a column and copy rows that meet the specified criteria and paste them in another sheet. The issue I am having is the value I am after is a range and not a set value. So say 25000-50000 or 0-250000 or 500000+ (I am going to make a few different macros then combine everything for a button).

The initial code seems easy enough.
Code:
Dim wb As Workbook
Dim S1 As Worksheet
Dim S2 As Worksheet
Dim S3 As Worksheet

wb = ThisWorkbook
S1 = wb.Sheets("Home")
S2 = wb.Sheets("Report")
S3 = wb.Sheets("FOLLOW UP")

For each cell in S3.range("w:w")
    if cell.value = "here is my problem spot" then
        matchrow = cell.row
        rows(matchrow & ":" & matchrow).select
        selection.copy
    
        S2.select
        activesheet.rows(matchrow).select
        activesheet.paste
        S3.select
    End if
next
I know that isn't the best way to copy/paste, but I am having problems remembering the other method I used in the past.
But regardless, what would be the correct way to define the range in values I am after?
 

Gasman

Enthusiastic Amateur
Local time
Today, 07:25
Joined
Sep 21, 2011
Messages
14,050
When I started sending emails from an Excel workbook, I used code that would filter for the rows to process and just use those?

Code:
' First set the filter to get just the rows we want
With ActiveSheet
    .AutoFilterMode = False
    .Range("A1:" & strLastCell).AutoFilter
    Selection.AutoFilter Field:=8, Criteria1:="Yes"
End With
Whilst I walked each row in turn, you could just copy the visible cells?

Code:
For Each rngCell In Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row).SpecialCells(xlCellTypeVisible)

HTH
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 15:25
Joined
May 7, 2009
Messages
19,169
you can google how to AutoFilter a column
the copy/paste the result to the other sheet.
 

Users who are viewing this thread

Top Bottom