first available row

mortgageman

Well-known Member
Joined
Jun 30, 2005
Messages
2,015
I tried searching on the board (cuz I know its been here a lot :oops: ) but I just couldn't find it. I am using the following line to get the first available row in column A


fa = [checking!a65536].End(xlUp).row + 1

How do I change it so I am not hard coding the 65536?
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
Try:

Code:
With Sheets("checking")
   fa = .Range("A" & .Rows.Count).End(xlup).Row + 1
End With
Denis
 
Upvote 0
I'm not an expert with VBA and may not understand what is being asked here . . . but used in this way, doesn't Rows.Count return 65536 anyway? And in any case, getting back to the original question about hard coding the 65536, . . . wouldn't it be easier to type the 5 characters of '65536' than the 10 characters of 'Rows.Count' - let alone the additional "" around the A and also the additional &?
 
Upvote 0
I'm not an expert with VBA and may not understand what is being asked here . . . but used in this way, doesn't Rows.Count return 65536 anyway? And in any case, getting back to the original question about hard coding the 65536, . . . wouldn't it be easier to type the 5 characters of '65536' than the 10 characters of 'Rows.Count' - let alone the additional "" around the A and also the additional &?
Peter

If you think about the change in Office12, 65536 is no more useful.

So Rows.Count/Columns.Count will return the precise number of Rows/Columns...
 
Upvote 0
I'm not an expert with VBA and may not understand what is being asked here . . . but used in this way, doesn't Rows.Count return 65536 anyway? And in any case, getting back to the original question about hard coding the 65536, . . . wouldn't it be easier to type the 5 characters of '65536' than the 10 characters of 'Rows.Count' - let alone the additional "" around the A and also the additional &?
Peter

If you think about the change in Office12, 65536 is no more useful.

So Rows.Count/Columns.Count will return the precise number of Rows/Columns...
Thanks jindon, I haven't seen Excel12 yet so certainly wasn't thinking of that. However, the thrust of the original question did not seem to be about how to cope with different versions of Excel but how to avoid coding the 65536.

Anyway, I look forward to getting hold of Excel12 in due course - it seems like there are some good new features. On the slightly negative side, it will make providing answers in a forum like this a bit harder as I think there will be more major differences than in recent version upgrades. Is that your view?
 
Upvote 0
Peter,

To me, it is just ealiser to remember the words than the specific numbers.

Certainly "IV"/256 is much shorter than Columns.Count though...
 
Upvote 0

Forum statistics

Threads
1,213,504
Messages
6,114,016
Members
448,543
Latest member
MartinLarkin

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