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

 

[DanteAmor]

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

Note: from column A to H there are 8 positions, in your example you are only transposing 7 positions.

 

[pwill]

Thank you DanteAmor much appreciated, can't try it out just yet but will let you know how I get on.

Thanks for the Note it should be "A:G"

pwill

 

[DanteAmor]

Ok, take your time, let me know if you have any doubt.

 

[pwill]

Hi DanteAmor,

I ran your code and I am getting

Run-time error '1004':
Application-defined or object-defined error

lc = Range("DataC").Cells(2, Columns.Count).End(xlToLeft).Column + 1 Range("DataC").Cells(1, lc).Resize(7, 1).Value = Evaluate("transpose(Data!A3:H3)") [CODE]

Any thoughts on what's is yapping or where I am going wrong?

pwill

 

[DanteAmor]

Do you have a range name? What is it name?
Did you modify something else in the macro?

 

[pwill]

The named range is "DataC"

 lc = Range("DataC").Cells(2, Columns.Count).End(xlToLeft).Column + 1 
Range("DataC").Cells(1, lc).Resize(7, 1).Value = Evaluate("transpose(Data!A3:G3)")

 

[pwill]

Sorry for the typo's I was using my phone

I didn't modify anything else?

pwill

 

[DanteAmor]

the DataC range is on the same "data" sheet?

 

[pwill]

Yes it's on the same sheet, I've tried everything but nothing seems to work? Not sure what I'm doing wrong?