Creating Ranges based on column data

[sethos]

Hello All,

I'm newer to Excel Macros and I have a lengthy manual data manipulation I'm trying to automate as much as I can. I get data in an excel sheet with many different columns and almost 10k rows and it needs parsed out to about 7 different sheets within the workbook based on the starting cell value in one column. I posted example data below. Basically I want to create a range variable, say Test_Range, and add the rows where the value in column D start with "H" so that I can cut and paste to another workbook. I'm lost as far as appending the variable. I figured I'd be using a For loop to iterate through the table (Like For Each cell In [D:D]), and then an If cell.value Like "H*" Then, but I don't understand how to add the range("A2:H2") to the variable while within the If statement but using variables. This is my thought process thus far and my current block.

Sub Parsed_Data()

Dim Test_Range as Range
For Each cell In [D:D]
-If cell.value Like "H*" Then
--Dim Row_Num As Integer
--Row_Num = cell.Row
--Dim Found_Range As Range
--Set Found_Range = ("A + Row_Num:H + Row_Num")
--Test_Range = Union(Test_Range, Found_Range)
--Else
--Endif
-Endif
Next cell

End Sub

I'm getting errors on the Line --Set Found_Range = ("A + Row_Num:H + Row_Num") for type mismatch.

I hope I'm making sense.

 

[gordsky]

would you not be better using the VBA code to FILTER the relevant column and then using specialcells to copy the data across?

 

[mohadin]

Hi
AS a beginner

 Set Found_Range = Range("A" & Row_Num & ":" & H" & Row_Num)

Or

 Set Found_Range = Range("A" & Row_Num & ":H" & Row_Num)

THen

TRy

Sub Parsed_Data()
    Dim Test_Range As Range
    Dim Found_Range As Range
    Dim cell As Range
    Dim Row_Num As Integer
    For Each cell In [D:D]
        If cell.Value Like "H*" Then
            Row_Num = cell.Row
            Set Found_Range = Range("A" & Row_Num & ":H" & Row_Num)
            If Test_Range Is Nothing Then
                Set Test_Range = Found_Range
            Else
                Set Test_Range = Union(Test_Range, Found_Range)
            End If
        End If
    Next cell
    Test_Range.Select
End Sub

 

[gordsky]

If your objective is to simply to copy cells containing H to another worksheet i think the filter option would suit you

Sub Parsed_Date()
Dim ws1 As Worksheet, ws2 As Worksheet
Dim Lrow As Long
Dim Cpy As range

Set ws1 = Sheets("YourSourceSheetName")   ' change as appropriate
Set ws2 = Sheets("YourDestinationSheetName")  '  change as appropriate

If ws1.AutoFilterMode Then ws1.AutoFilterMode = False

   Lrow = ws1.UsedRange.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).row

   ws1.range("$A$1:$H" & Lrow).AutoFilter
   ws1.range("$A$1:$H" & Lrow).AutoFilter Field:=8, Criteria1:="H*", Operator:=xlFilterValues
       
   ws1.range("$A2:$H" & ws1.Cells(Rows.count, 1).End(xlUp).row).Copy
   ws2.range("A1").PasteSpecial xlPasteValues '  change the A1 reference if you want them pasted to a different cell

End Sub

 

[sethos]

Hi
AS a beginner

 Set Found_Range = Range("A" & Row_Num & ":" & H" & Row_Num)

Or

 Set Found_Range = Range("A" & Row_Num & ":H" & Row_Num)

THen

TRy

Sub Parsed_Data()
    Dim Test_Range As Range
    Dim Found_Range As Range
    Dim cell As Range
    Dim Row_Num As Integer
    For Each cell In [D:D]
        If cell.Value Like "H*" Then
            Row_Num = cell.Row
            Set Found_Range = Range("A" & Row_Num & ":H" & Row_Num)
            If Test_Range Is Nothing Then
                Set Test_Range = Found_Range
            Else
                Set Test_Range = Union(Test_Range, Found_Range)
            End If
        End If
    Next cell
    Test_Range.Select
End Sub

Thanks so much for the guidance. I think this will work best for me as there will be multiple applications of variants of this code throughout the whole automation. I am getting a Type Mismatch error when I try to use

 Set Found_Range = Range("A" & Row_Num & ":" & H" & Row_Num)

Or:

 Set Found_Range = Range("A" & Row_Num & ":H" & Row_Num)

The error highlights the last & before the last Row_Num. Any insight?

 

[sethos]

If your objective is to simply to copy cells containing H to another worksheet i think the filter option would suit you

Sub Parsed_Date()
Dim ws1 As Worksheet, ws2 As Worksheet
Dim Lrow As Long
Dim Cpy As range

Set ws1 = Sheets("YourSourceSheetName")   ' change as appropriate
Set ws2 = Sheets("YourDestinationSheetName")  '  change as appropriate

If ws1.AutoFilterMode Then ws1.AutoFilterMode = False

   Lrow = ws1.UsedRange.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).row

   ws1.range("$A$1:$H" & Lrow).AutoFilter
   ws1.range("$A$1:$H" & Lrow).AutoFilter Field:=8, Criteria1:="H*", Operator:=xlFilterValues
      
   ws1.range("$A2:$H" & ws1.Cells(Rows.count, 1).End(xlUp).row).Copy
   ws2.range("A1").PasteSpecial xlPasteValues '  change the A1 reference if you want them pasted to a different cell

End Sub

Thanks for the reply! This first stage my goal is to cut and paste, but I will have to do multiple times to various columns/Criteria and then do a lot of sorting/filtering after the data gets to the appropriate sheets. Also, doesn't filtering copy the hidden rows to the clipboard as well? (I may be wrong)

 

[BSALV]

Also, doesn't filtering copy the hidden rows to the clipboard as well? (I may be wrong)

No !
But your data was arranged in a table, when i see those headers, then filtering and copying is even simpler then #4.

 

[sethos]

Thanks so much for the guidance. I think this will work best for me as there will be multiple applications of variants of this code throughout the whole automation. I am getting a Type Mismatch error when I try to use

 Set Found_Range = Range("A" & Row_Num & ":" & H" & Row_Num)

Or:

 Set Found_Range = Range("A" & Row_Num & ":H" & Row_Num)

The error highlights the last & before the last Row_Num. Any insight?

Nevermind I had something misspelled and was able to figure it out. Thank you so much!

 

[sethos]

No !
But your data was arranged in a table, when i see those headers, then filtering and copying is even simpler then #4.

Oh okay! I'll throw it in the macro and report back. I knew I was making it harder than it needed to be!

 

[sethos]

Oh okay! I'll throw it in the macro and report back. I knew I was making it harder than it needed to be!

Okay so This code just cut the entire dataset, not just the ones I wanted through filtering. I'm not sure what happened though.