how mnay rows, columns in a sheet

[MarkElls]

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.

 

[xlbo]

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

 

[MarkElls]

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

 

[MarkElls]

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

My apologies.

Thanks.
Mark.

 

[xlbo]

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

 

[Loomah]

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?
How do I get the best answers?​

 

[MarkElls]

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.

 

[KenWright]

LOL - Mark, I think Loomah was referring to his own post

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

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[Loomah]

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?
How do I get the best answers?​