Find Last Column and Last Row in Named Range

pwill

Active Member
Joined
Nov 22, 2015
Messages
406
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

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

ie

ABCDEFGHIJK
1
2
3135791113
4
51234567
63
75
87
99
1011
1113
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...

ABCDEFGHIJK
1
2
32468101214
4
51234567
634
756
878
9910
101112
111314
12
13
14
15

<tbody>
</tbody>


Any help would be much appreciated

Regards

pwill
 

Some videos you may like

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,594
Office Version
  1. 2007
Platform
  1. Windows
Try this. Change "NameRange" by your Named Range.

Code:
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

Active Member
Joined
Nov 22, 2015
Messages
406
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
 
Last edited:

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,594
Office Version
  1. 2007
Platform
  1. Windows
Ok, take your time, let me know if you have any doubt.
 

pwill

Active Member
Joined
Nov 22, 2015
Messages
406

ADVERTISEMENT

Hi DanteAmor,

I ran your code and I am getting

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

Code:
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
 
Last edited:

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,594
Office Version
  1. 2007
Platform
  1. Windows
Do you have a range name? What is it name?
Did you modify something else in the macro?
 

pwill

Active Member
Joined
Nov 22, 2015
Messages
406

ADVERTISEMENT

The named range is "DataC"
Code:
 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)")
 
Last edited:

pwill

Active Member
Joined
Nov 22, 2015
Messages
406
Sorry for the typo's I was using my phone

I didn't modify anything else?

pwill
 
Last edited:

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,594
Office Version
  1. 2007
Platform
  1. Windows
the DataC range is on the same "data" sheet?
 

pwill

Active Member
Joined
Nov 22, 2015
Messages
406
Yes it's on the same sheet, I've tried everything but nothing seems to work? Not sure what I'm doing wrong?
 

Watch MrExcel Video

Forum statistics

Threads
1,123,530
Messages
5,602,190
Members
414,513
Latest member
junbuggle

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top