Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations sizbut on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

How to count rows in Excel before importing into Access? 3

Status
Not open for further replies.
Tethys

Tethys

Programmer
Dec 19, 2005
4
US
Hi everyone.

I've an issue needing some help. I am trying to count the number of rows in a worksheet before copying the data over into an Access table. What I've tried so far is giving me an overflow issue - which makes me wonder if I'm just counting every single row in the worksheet, regardless of it containing data or not (that's a lot of rows...).

What I need to be able to do:
- Count rows in spreadsheet that have data in them. Some rows will have partial data, meaning that some cells in any given row will not be populated. The first row to not contain any data is considered EOF. And I will be using DAO for this.

I'll paste below my latest two attempts.

1)
Dim strStorage as integer
strStorage = CLng(xlsWST.Rows.Count)

2)
Dim strStorage as integer
strStorage = xlsWST.Range("Worksheet 1").Rows.Count


Thank you for any helpful input =).
-Tethys
 
Sort by date Sort by votes


Hi,

Your problem is INTEGER.

Integer is limited to 32,767.

Excel has 65,536 row nax

Use Long instead.

Skip,

[glasses] [red]Be Advised![/red]
The band of elderly oriental musicians, known as Ground Cover, is, in reality...
Asian Jasmine![tongue]
 
Upvote 0 Downvote
Oh, heh...*sigh*

I do seem to be able to count every row now...and I suppose that is what that line is trying to do.

What I'd LIKE to do, though, is only count those rows that have data in them. That is where I was hoping to lead the above question to. If it helps any, column B will always have data in it.

With xlsWST.Range I've tried various ways to accomplish this. But using range expects both a column and row count. The row count is what I don't know.

Any suggestions?
 
Upvote 0 Downvote



Try using the COUNT or COUNTA spresdsheet functions
[tt]
Dim lRowCount as Long
lRowCount = Excel.COUNTA(Sheet1.Range("A:A"))
[/tt]
for instance

Skip,

[glasses] [red]Be Advised![/red]
The band of elderly oriental musicians, known as Ground Cover, is, in reality...
Asian Jasmine![tongue]
 
Upvote 0 Downvote
Why don't you make the column count 1? Shouldn't that give you your desired results? Or are you trying to find how many cells have data in them?

-------------------------
Just call me Captain Awesome.
 
Upvote 0 Downvote
Something like this ?
Dim strStorage As Long
strStorage = xlsWST.Cells.Find(What:="*", SearchDirection:=2, SearchOrder:=1).Row '2=xlPrevious, 1=xlByRows

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886
 
Upvote 0 Downvote
I don't know how many cells in any given column (column B, for instance) have data. If column B were my ID column, and it were always populated, counting each cell in this column will give me the row count I am after.

I'll give COUNT or COUNTA a shot. Perhaps I'm not implementing Range correctly, but each time I've tried using it I get stuck with what end range to use....since again I don't know the max, or final, row count.
 
Upvote 0 Downvote


You can also use the UsedRange property
Code: 
lRowCount = SomeSheet.UsedRange.Rows.Count


Skip,

[glasses] [red]Be Advised![/red]
The band of elderly oriental musicians, known as Ground Cover, is, in reality...
Asian Jasmine![tongue]
 
Upvote 0 Downvote
Try my suggestion, you'll get the last used row number in your sheet.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886
 
Upvote 0 Downvote
Thanks PHV and Skip, both of your suggestions do indeed work great!

Such an easy result for such a lengthy headache =D. Many thanks to the responses here. Have a good one.

-Tethys
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

DarkOne72
  • Locked
  • Question
MS Access DoCmd.Transferdatabase Assistance
Replies
2
Views
2K
lameid
lameid
Viswa.R
  • Locked
  • Question
Dimension import from Autocad to Excel
Replies
0
Views
386
Viswa.R
Viswa.R
FrankMars
  • Locked
  • Question
Exporting Records to Excel 1
Replies
6
Views
524
Andrzejek
Andrzejek
BFroeschlKC
  • Locked
  • Question
Publisher Automation from Access Frustration
Replies
3
Views
2K
dhookom
dhookom
btamsgn
  • Locked
  • Question
How to edit column chitoi into TableA
Replies
0
Views
166
btamsgn
btamsgn

Part and Inventory Search

Sponsor

Back
Top