Pls help on VBA code: Table to list etc

[mrchonginhk]

Hi I have a table like this

VBAAssignment1.xls
	A	B	C	D	E
1	Q2
2		Col1	Col2	Col3	Col4
3	Row1	1	2	3	4
4	Row2	5	6	7	8
5	Row3	9	10	11	12
6	Row4	13	14	15	16
7	Row5	17	18	19	20
Q2

Then, I need to write a FUNCTION called
Tableonly that returns a table as range without extraneous emoty rows and column. But, if user clicks a cell outside table, return only cell where user has clicked. Hints from my boss: Use COUNTA and range.EntireRow.

Then using above write program that selects only data from table (Bosss say use range.size method)

Then write another program select only col heading
Then write another select only row headings

Hence, write a program converting the table in a list. ie the table above should becomes:-

VBAAssignment1.xls
	G	H	I	J
6	Row1	Col1	1
7	Row2	Col1	5
8	Row3	Col1	9
9	Row4	Col1	13
10	Row5	Col1	17
11	Row1	Col2	2
12	Row2	Col2	6
13	Row3	Col2	10
14	Row4	Col2	14
15	Row5	Col2	18
16	Row1	Col3	3
17	Row2	Col3	7
18	Row3	Col3	11
19	Row4	Col3	15
20	Row5	Col3	19
21	Row1	Col4	4
22	Row2	Col4	8
23	Row3	Col4	12
24	Row4	Col4	16
25	Row5	Col4	20
Q2

Pls help. Any one part will help ... Thanks.

 

[mrchonginhk]

Any one can help at least one part ??

 

[mrchonginhk]

Pls help

 

[mrchonginhk]

Forget to tell...

The numbers 1,2,3, 4 etc in the 1st table should be fixed values instead of formula.

Thanks.

 

[mrchonginhk]

Forget to tell...

The numbers 1,2,3, 4 etc in the 1st table should be fixed values instead of formula.

Thanks.

 

[phantom1975]

Give this a whirl:

Book1
	A	B	C	D	E
1
2		Col1	Col2	Col3	Col4
3	Row1	1	2	3	4
4	Row2	5	6	7	8
5	Row3	9	10	11	12
6	Row4	13	14	15	16
7	Row5	17	18	19	20
8	Row1	Col1	1
9	Row2	Col1	5
10	Row3	Col1	9
11	Row4	Col1	13
12	Row5	Col1	17
13	Row1	Col2	2
14	Row2	Col2	6
15	Row3	Col2	10
16	Row4	Col2	14
17	Row5	Col2	18
18	Row1	Col3	3
19	Row2	Col3	7
20	Row3	Col3	11
21	Row4	Col3	15
22	Row5	Col3	19
23	Row1	Col4	4
24	Row2	Col4	8
25	Row3	Col4	12
26	Row4	Col4	16
27	Row5	Col4	20
Sheet1

And the code:<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> TableOnly()<SPAN style="color:#00007F">Dim</SPAN> EndColumn<SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">Long</SPAN><SPAN style="color:#00007F">Dim</SPAN> EndRow<SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">Long</SPAN><SPAN style="color:#00007F">Dim</SPAN> ThisRow<SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">Long</SPAN><SPAN style="color:#00007F">Dim</SPAN> ThisColumn<SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">Long</SPAN><SPAN style="color:#00007F">Dim</SPAN> ThisCell<SPAN style="color:#00007F">As</SPAN> Range

    EndColumn = Range("IV2").End(xlToLeft).Column
    EndRow = Range("A65536").End(xlUp).Row
    <SPAN style="color:#00007F">For</SPAN> ThisColumn = Range("A2").End(xlToRight).Column<SPAN style="color:#00007F">To</SPAN><SPAN style="color:#00007F">End</SPAN>Column
        <SPAN style="color:#00007F">For</SPAN> ThisRow = Range("A1").End(xlDown).Row To EndRow
            <SPAN style="color:#00007F">Set</SPAN> ThisCell = Range("A65536").End(xlUp).Offset(1)
            ThisCell.Value = Cells(ThisRow, 1)
            ThisCell.Offset(, 1).Value = Cells(2, ThisColumn)
            ThisCell.Offset(, 2).Value = Cells(ThisRow, ThisColumn)
        <SPAN style="color:#00007F">Next</SPAN> ThisRow
    <SPAN style="color:#00007F">Next</SPAN> ThisColumn

End<SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[mrchonginhk]

Thank you so much !!!

How to make it a FUNCTION ?

 

[phantom1975]

A function won't do the work for you. Think of a FUNCTION as being a math equation. A function will just spit out an answer for you, it won't do the work.

 

[mrchonginhk]

Thx
Ok forget about formula....

I created a button and assign your code to it and then I select the range and click the button but nothing happen. Did I miss some step/s ?

 

[mrchonginhk]

Sorry... it did work... !! Thanks.

Then, one final help are 3 small codes-

Write program that selects only data from original table (Bosss say use range.size method)

Then write another program select only col heading
Then write another select only row headings