Newbie question

I have an Excel spreadsheet with 1 column and about 50-60 rows. Could someone post the code to tell how many rows there are? I can't figure it out
Thanks

In Excell, isn't there numbers next to the columns?

Yes there is, but, The excel sheet is never opened. There is about 30 spreadsheets and I have a flexgrid that displays the info on them. I have to know how many rows however. Anyone with some simple code?

Hi,

Use this macro, and whenever you wnat to know how many rows, click on TOOLS > MACRO > RUN > GETCOLSNUMBER
and a messagebox will pop up etc


Sub GetColsNumber()

Dim i As Integer
Dim test As Boolean

test = False
On Error Resume Next
Do
i = i + 1
If Trim(Sheet1.Cells(i, 1)) = "" Then
MsgBox i - 1 & " Rows", 64, "information."
test = True
Exit Sub
End If
If Err <> 0 Then test = True
Debug.Print i
Loop Until test
On Error Resume Next

End Sub


DocZaf
{;->

Sub GetColsNumber()

Dim i As Integer
Dim test As Boolean

test = False
On Error Resume Next
Do
i = i + 1
If Trim(Sheet1.Cells(i, 1)) = "" Then
MsgBox i - 1 & " Rows", 64, "information."
test = True
Exit Sub
End If
If Err <> 0 Then test = True
Debug.Print i
Loop Until test
On Error Resume Next

End Sub

Use this macro
and click on TOOLS > MACRO > RUN > GETCOLUMNSNUMBER

A message box popups etc....

DocZaf
{;->

Thanks for the help, but can I run this within my program. The program opens different excel files and needs to know how many rows before it displays them

Sure you can create it a macro and paste the sub i complete as it appears there, If in doubt consult the help on how to create a macro

Zaf

Steel!

I thought I helped you on this subject yesterday already!

Anyway, you need to use GetObject and connect to the file and grab the row number.

Use something like this.

Code:

---

  'Reference "Microsoft Excel 9.0 Library"
  Dim xlsWorkBook As Excel.Workbook
  Dim xlsWorkSheet As Excel.Worksheet
 
  Set xlsWorkBook = GetObject("C:\City Charts.xls")
  Set xlsWorkSheet = xlsWorkBook.Worksheets("C")
  Text1.Text = xlsWorkSheet.Cells(1, 1).Value
  xlsWorkBook.Close

---

Program it exactly like the way how you would program in Excel, except put "xlsworksheet." Use this code combine with what I posted for you yesterday and you should have it.

Use the property "Userrange" with "EntireColumn" or "Column".

If you have furthur problems, I need more detail.

[Edited by Nitro on 05-02-2000 at 04:12 PM]

Thanks for all the help everyone!

You did help Nitro :) I just was having trouble looking it up.
I do appreciate your eagerness to help me. Have you been doing VB for awhile now?

Hello Steel!

Glad that I could help! I started off with Access and Excel VBA. I have been doing Excel VBA for a long time and just started doing 2-tiers design with VB, about 3 years ago.

API is new to me and have only started using it for a year now. There are a couple of guys on this site that are really good with them.

If I can be of any other assistant in Excel VBA, feel free to post again.

[Edited by Nitro on 04-26-2000 at 11:26 AM]

Thanks Nitro