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.

 

[BSALV]

2 sheets, MySheet1 and MySheet2, with in MySheet1 a table named TBL_1 (see below)

Sub Filter_Copy()
     Dim LO
     Set LO = Sheets("MySheet1").ListObjects("TBL_1")           'your table
     With LO.Range                                              'range = headers & databody of your table
          .AutoFilter                                           'reset autofilter
          .AutoFilter 3, "<=.5"                                 'filter on 3rd column for values less or equal to .5
          LO.DataBodyRange.Copy Sheets("MySheet2").Range("A" & Rows.Count).End(xlUp).Offset(1)     'copy filtered values in the databody to another sheet
          .AutoFilter
     End With
End Sub

Map2
	A	B	C	D	E
1	a	b	c	d	e
2	0,332	0,441	0,786	0,939	0,227
3	0,290	0,990	0,363	0,460	0,517
4	0,399	0,171	0,659	0,376	0,990
5	0,074	0,653	0,701	0,231	0,085
6	0,835	0,437	0,413	0,330	0,036
7	0,622	0,031	0,146	0,826	0,300
8	0,596	0,579	0,887	0,744	0,474
9	0,743	0,131	0,601	0,878	0,694
10	0,783	0,622	0,840	0,145	0,635
11	0,608	0,624	0,281	0,601	0,838
12	0,056	0,150	0,203	0,284	0,506
13	0,342	0,525	0,157	0,391	0,194
14	0,210	0,593	0,704	0,109	0,349
15	0,290	0,418	0,127	0,624	0,389
16	0,882	0,118	0,533	0,136	0,566
17	0,931	0,400	0,692	0,237	0,507
18	0,966	0,810	0,310	0,277	0,226
19	0,083	0,041	0,855	0,805	0,920
20	0,066	0,824	0,745	0,011	0,723
21	0,819	0,694	0,030	0,791	0,439
22	0,333	0,466	0,043	0,967	0,261
23	0,749	0,523	0,472	0,754	0,795
MySheet1