How to count used range of cells in Excel sheet (VBA)

[Yamishon_Noah]

Hi,

I am working on some VBA program and I want to know in worksheet how many cells used (has value) in column 1

incase in excel VBA I use as follow

Sub count ()

Dim RowCount as Double

RowCount = Application.WorksheetFunction.CountA(Range(Range("A2"), Range("A2").End(xlDown)))

End Sub

(I posted above code for reference only)

How can I achieve above in Autocad VBA? like from autocad VBA how to count range of used cells in particular excel worksheet?

(Pls note - I am able to open my worksheet in full program)

[Alfred.NESWADBA · Accepted solution]

Hi,

>> How can I achieve above in Autocad VBA? 

In the same way as in Excel-VBA as long as you have added the Excel references.

The only difference is that the AutoCAD VBA modules are not part of the Excel Workbook or Worksheet, so you can't expect that AutoCAD has already access to the Workbook or Worksheet.

I looked a bit into it (please understand that I'm far away from being an Excel expert!), that is what I get working:

Public Sub test()
   Dim tXlsObj As Excel.Application
   Set tXlsObj = GetObject(, "Excel.Application")
   
   Dim tSheet As Excel.Worksheet
   Set tSheet = tXlsObj.ActiveSheet
   
   Dim tR As Excel.Range
   Set tR = tSheet.Range(tSheet.Range("A2"), tSheet.Range("A2").End(xlDown))
   
   Dim tRowCount As Integer
   tRowCount = Excel.WorksheetFunction.CountA(tR)
End Sub

HTH, - alfred -

[Yamishon_Noah]

Hi Alfred,

Thanks a lot.

That worked and I learned.

How can I learn AutoCAD VBA? I could not find any proper resource or channel...

can you guide me where I can learn?

Yamishon