VBA dynamic range and autofill based on named range

A

aivoryuk

Board Regular
Joined
Nov 18, 2009
Messages
130
Hi I need a little help with some VBA coding.

I have used the following to find the last cell.

Sub LastCellInColumn()
Range("B65000").End(xlUp).Select

This works fine.

However when I substitute the B65000 to a named range "chttotal" it just gives me cell B1.

How can I get around this?

The 2nd part of this is I am importing a spreadheet onto another sheet which uses named ranges. On the spreadheet to where the data is imported to there is a column where there is a formula. I would like this formula to update (autofill) as a named range ("chttotal") or to the length of the column that has been imported.

hope this makes sense
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
Sort by date Sort by votes
aivoryuk said:
Hi I need a little help with some VBA coding.

I have used the following to find the last cell.

Sub LastCellInColumn()
Range("B65000").End(xlUp).Select

This works fine.

However when I substitute the B65000 to a named range "chttotal" it just gives me cell B1.

How can I get around this?

The 2nd part of this is I am importing a spreadheet onto another sheet which uses named ranges. On the spreadheet to where the data is imported to there is a column where there is a formula. I would like this formula to update (autofill) as a named range ("chttotal") or to the length of the column that has been imported.

hope this makes sense
Click to expand...

Does this work?

Range("chttotal").Cells(rows.count,2).End(xlUp).select
 
Upvote 0
John Davis said:
Does this work?

Range("chttotal").Cells(rows.count,2).End(xlUp).select
Click to expand...

Hi ues it does although I have to change it slightly (rows.count,1)

I did manage to find this out

Range("chttotal").Select
Selection.End(xlDown).Select

but if the if cell B1 is the only cell to have data in it jumps to to the last cell in "chttotal" not sure why that is.

Any idea on once I've selected the last cell in the named range I can then get it to autofill to the same number of cells as another named range?
 
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Question
2-step Dynamic Ranges and Charts
Replies
0
Views
206
Nick Loughton
N
R
  • Solved
VBA - Summing a dynamic gap range between two named ranges?
2
Replies
13
Views
360
RockandGrohl
R
J
  • Question
Dynamic named range
Replies
1
Views
51
dermie_72
D
SanjayGMusafir
  • Solved
Can we make reference to Named Ranges Dynamic?
Replies
1
Views
354
SanjayGMusafir
SanjayGMusafir
L
  • Solved
converting references from static to dynamic
Replies
5
Views
184
Joe4
Joe4

Forum statistics

Threads
1,215,029
Messages
6,122,760
Members
449,095
Latest member
m_smith_solihull

Share this page

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
Back
Top