Select Subset of Range in VBA based on value

Question

I am trying to select a subset of data in a range and copy it to another sheet in Excel. My thought process was as follows

Find the first and last instance of the value in a column
Copy from the first instance down to the last instance and all the way to the Right
Paste the range of data into my sheet

I am having trouble finding the first and last instance of the value in a column and setting the range to copy.

This is what I have so far:

Sub Button()
  'Application.ScreenUpdating = False
  Dim wb As Workbook
  Dim ws_temp, ws_data As Worksheet
  Dim data_rng As Range

  Set wb = ThisWorkbook
  Set ws_temp = wb.Sheets("Template")
  Set ws_data = wb.Sheets("data")
  'declare id
  Dim id As Integer
  Set id= ws_temp.Range("B4").Value 'this is where the value is stored 

  'find first instance of id in data.
  ' The data starts in cell A6 and extends to column O. The number of rows will be variable
  'find last instance of pitcher id

  'Set data_rng
  Set data_rng = wb.ws_data.Range("first instance":O&"second instance")

  'Copy data_rng
  data_rng.Copy wb.ws_temp.Range("A8") 'the data will always go in cell A8 on the Template sheet

  'Application.ScreenUpdating = True
End Sub

Did you consider using Find, specifying the After parameter? — BigBen
– BigBen, Commented May 18, 2021 at 18:59
Set id= will give you an error. Only Object variables need to be Set. Value variables like Integers dont need to be Set. Remove Set, just do id=. — Toddleson
– Toddleson, Commented May 18, 2021 at 19:05
After you have the first and second instance (from Range.Find like BigBen suggested), you can define your finished range like With wb.ws_data: Set data_rng = .Range(FirstInstance, .Cells(SecondInstance.Row,.Columns.Count)): End With which will get you the range you described in your post. — Toddleson
– Toddleson, Commented May 18, 2021 at 19:12
No I have not used Find before. Are these full answers? I would like to give credit where credit is due. — Jack Armstrong
– Jack Armstrong, Commented May 18, 2021 at 20:18
@JackArmstrong Is the data in the column sorted so that all the instances you are interested in are in a contiguous range? — norie
– norie, Commented May 18, 2021 at 20:20

norie · Accepted Answer · 2021-05-18 21:53:06Z

1

What you could do is use Application.Match to find the row of the first instance then use Application.CountIf to count the no of occurrences of the search term.

With that information you should be able to get the subset of the data you want to copy using Resize.

Sub Button()

Dim wb As Workbook
Dim ws_temp, ws_data As Worksheet
Dim data_rng As Range
Dim id As Long
Dim lngFirstRow As Long
Dim lngNoRows As Long

    Application.ScreenUpdating = False
    
    Set wb = ThisWorkbook
    Set ws_temp = wb.Sheets("Template")
    Set ws_data = wb.Sheets("data")
    
    ' get search id
    id = ws_temp.Range("B4").Value

    ' find row of first occurence of id
    lngFirstRow = Application.Match(id, ws_data.Columns(2), 0)
    
    ' find count of id
    lngNoRows = Application.CountIf(ws_data.Columns(2), id)
    
    Set data_rng = ws_data.Range("B" & lngFirstRow).Resize(lngNoRows, 14)
      
    'Copy data_rng
    data_rng.Copy ws_temp.Range("A8")    'the data will always go in cell A8 on the Template sheet

    Application.ScreenUpdating = True
    
End Sub

edited May 18, 2021 at 21:53

answered May 18, 2021 at 20:34

norie

9,9372 gold badges14 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Jack Armstrong Over a year ago

There is a type mismatch error when finding row of first occurence of id. I think this is cause it returns a double not a long

norie Over a year ago

Is the value you are looking for definitely in column A on the sheet 'data'? Is the value from B4 on 'Template' the same data type as the values in column A on 'data'?

Jack Armstrong Over a year ago

The value I am looking for is on 'data' in column B. I need to return columns B-O or A-O. I can live without returning column A. I am receiving an error when setting lngFirstRow as Error 2042. I have declared them as Variant's, but still receive mismatch error.

norie Over a year ago

I'll update the code to look in column B rather than column A.

Jack Armstrong Over a year ago

I see what is happening. So it does select the data from column B, but it does not copy and paste it.

|

Collectives™ on Stack Overflow

Select Subset of Range in VBA based on value

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related