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!

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

how mnay rows, columns in a sheet

Status
Not open for further replies.

MarkElls

Programmer
Aug 26, 2003
57
GB
Brief:
I need to get data out of Excel into $ seperated file. Not a csv. Each row of data should have the same number of columns.

Problem:
I like simple code. Normally I manually loop from top to bottom of an excel sheet by using hard coded values and ensuring activecell.value<>"".

Is there an easy way such as

Dim RowIndex as Integer
Dim ColIndex as Integer

For RowIndex = 1 to ActiveSheet.LastRow
For ColIndex = 1 to ActiveSheet.LastColumn
.... do $ adding code ....
Next
Next

I have the stuff to write to file. I can do the loops I just want to make it read as easy as possible.

Cheers.
Mark.

 
For RowIndex = 1 to ActiveSheet.usedrange.rows.count
For ColIndex = 1 to ActiveSheet.usedrange.columns.count

should do the trick for you

Rgds, Geoff

"Having been erased. the document thjat you are seeking. Must now be retyped"

Please read FAQ222-2244 before you ask a question
 
Xlbo,
I tried this, I have 3 rows of eight columns and the data returned via the above method gives 269 Rows and 24 Columns?

Any idea why?

Thanks.
Mark
 
What a plonker I found a cell with data in further down.

My apologies.

Thanks.
Mark.
 
You have formatting or formulae in otherwise blank cells ??
You used to have more data in the workbook and havn't saved since you deleted it ??
gimme something more to work on here

If that isn't working, try

lRow = cells(65536,1).end(xlup).row)
lCol = cells(1,256).end(xltoleft).column

For RowIndex = 1 to lRow
For ColIndex = 1 to lCol

Alternatively, there are 2 FAQs on this...

Rgds, Geoff

"Having been erased. the document thjat you are seeking. Must now be retyped"

Please read FAQ222-2244 before you ask a question
 
Mark
Have a look in the FAQ section. Bothe xlbo and myself have written FAQs on finding the last Row or Column based on the fact that UsedRange is often unreliable.

Having said that, if you have rougue data then you will still get the same results using the code in the FAQs!

Does that make this a pointless post?

Happy Friday!

;-)
If a man says something and there are no women there to hear him, is he still wrong? [ponder]
How do I get the best answers?
 
If it was pointless I would not have asked the question.

I will have a a look at thje FAQ.

Hopefully this should not happen in the real application as the file should be data out of a db that will be sent in XL format and then exported to $ seperated for re-importing.

Many Thanks.
Mark.
 
LOL - Mark, I think Loomah was referring to his own post :)

Regards
Ken..............

----------------------------------------------------------------------------
[peace]It's easier to beg forgiveness than ask permission[2thumbsup]

----------------------------------------------------------------------------
 
Certainly was!!
Sorry for any misunderstanding but that's sometimes the problem with the written word.

Happy Friday, still!

;-)
If a man says something and there are no women there to hear him, is he still wrong? [ponder]
How do I get the best answers?
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top