Find Last Column and Last Row in Named Range

[pwill]

Hi can anyone help?

I have some Data in Range("A5:H5") and a named Range("A5:H11")

I get new set of results in Range ("A3:H3") after filtering some Data and then transpose the results into the first column of my named Range ("A5:A11") using the following code

[FONT=Verdana]Range("A5").Resize(7, 1).Value = Evaluate("transpose(Data!A3:H3)")[/FONT]

ie

	A	B	C	D	E	F	G	H	I	J	K
1
2
3	1	3	5	7	9	11	13
4
5	1	2	3	4	5	6	7
6	3
7	5
8	7
9	9
10	11
11	13
12
13
14
15

<tbody>
</tbody>

And with the next set of results in Range ("A3:H3") after filtering more Data, I want to transpose the results in the same way to the next available column in the named range but I am struggling to find the Next Empty Column within the Named Range so I can transpose the results using the same method as above.

ie after filtering more Data the next results would be transposed into Range ("B5:B11) and so on...

​

	A	B	C	D	E	F	G	H	I	J	K
1
2
3	2	4	6	8	10	12	14
4
5	1	2	3	4	5	6	7
6	3	4
7	5	6
8	7	8
9	9	10
10	11	12
11	13	14
12
13
14
15

<tbody>
</tbody>

Any help would be much appreciated

Regards

pwill

 

[pwill]

Try this. Change "NameRange" by your Named Range.

Sub test()
    Dim lc As Long
    
    lc = Range("[COLOR=#ff0000]NameRange[/COLOR]").Cells(2, Columns.Count).End(xlToLeft).Column + 1
    Range("[COLOR=#ff0000]NameRange[/COLOR]").Cells(1, lc).Resize(7, 1).Value = Evaluate("transpose(Data!A3:H3)")
    
End Sub

Thanks for ur help with this Dante Amor couldn't get it to work with the macro I am using but I'm sure it will come in useful

Pwill

 

[DanteAmor]

Take the test with my file

https://www.dropbox.com/s/rj2oj9dn85rgb23/range.xlsm?dl=0

 

[pwill]

Thanks Dante Amor, I downloaded your file and it works fine, it must be something to do with the macro I am using? If I find the problem I will let u know what it was but for now I have done a work-around using a few 'If' statements, it's long winded but does what I need

Regards

pwill

 

[DanteAmor]

ok, let me know if you have any doubt