CODEWITHSUNDEEP
excelexcel-2007vba
Sam
Sam

Reputation: 3177

Sheet_variable.UsedRange.Column does not give the expected/desired result. Why?

I'm interested in using a variable of type workbook in Excel VBA where I would hold an Excel file like this:

Set mWB = Application.Workbooks("C:\Central.xlsx")

and then I would like to hold a worksheet from this file similarly in a Sheet variable.

Set aWS = mWB.Sheets("Sheet1")

In Sheet1 inside Central.xlsx, I have values in Range(A1:D6)

However, when I do this: MsgBox aWS.UsedRange.Column , I get 1. I was expecting 6 (6 populated rows) and when I do this: MsgBox aWS.UsedRange.Row I get 1 again. I was expecting 4 (4 populated rows).

Where is the mistake please?

Upvotes: 0

Views: 3189

Answers (1)

assylias
assylias

Reputation: 328923

You probably meant to use MsgBox aWS.UsedRange.Columns.Count instead (Column with an s at the end) - same for rows.

Range.Row returns the first row of the range.

Upvotes: 1

Related Questions