VBA Macro Create a Table from Cell Address

[VbaAz]

Hello I Am Looking For VBA macro to create a table. The cell Address's though for the range of the table is bassed on the text in the cells. For Example A2 = "First Word" and D21 = "Second Word". I need a macro that will look up the cell address of each of those words and then create a table using that as the range. Ive looked all over and cannot find something with exactly what I need. I really appreciate any information.

Thank you!

 

[jim may]

This is a total shot in the dark... as I am not sure I understand all that you want.. but anyway, here goes...
If you sheet looks this way, with RangeNames set for Cell A2 and D21 of First_Word and Second_Word, respectively (RangeNames CANNOT contain spaces)...

Excel 2012
	A	B	C	D	E
1
2	First Word
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21				Second Word
22
23
Sheet1

Run this Macro Foo

Sub Foo()
    ActiveSheet.ListObjects.Add(xlSrcRange, _
    Range(Range("First_Word").Offset(-1), Range("Second_Word").Offset(-1)), , xlNo).Name = _
        "Table1"
    Set LO = Sheets("Sheet1").ListObjects("Table1")
    LO.DataBodyRange.Select
    Selection.ListObject.ListRows(1).Delete
    Range("A1:D21").Select
End Sub

Afterwards you will get: (Which is a Table: Table1)

Excel 2012
	A	B	C	D
1	Column1	Column2	Column3	Column4
2	First Word
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21				Second Word
Sheet1

 

[VbaAz]

Awesome Thank you! THat is exactly what i was looking for!

 

[jim may]

Thanks for your response., and Welcome to the Board. Jim May

 

[dosinger]

Jim - This is very specific, but I would like to build on it for the general case.

I have a list anywhere in the worksheet that reads:

California
San Diego
Los Angeles
San Francisco
Fresno
Carlsbad

The macro recorder is cell specific, so I can't figure out what edits to make.

Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.ListObjects.Add(xlSrcRange, Range("$I$12:$I$14"), , xlYes).Name = _
"Table2"
Range("Table2[[#All],[Alaska]]").Select

Thanks.

 

[jim may]

Sorry, but I can't help on this...

 

[dosinger]

Worth a shot.