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?
 

Some videos you may like

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

SydneyGeek

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251
Try:

Code:
With Sheets("checking")
   fa = .Range("A" & .Rows.Count).End(xlup).Row + 1
End With
Denis
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,868
Office Version
  1. 365
Platform
  1. Windows
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 &?
 

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995

ADVERTISEMENT

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...
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,868
Office Version
  1. 365
Platform
  1. Windows
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?
 

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
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...
 

Watch MrExcel Video

Forum statistics

Threads
1,114,052
Messages
5,545,734
Members
410,702
Latest member
clizama18
Top