0

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?

Community
  • 1
  • 1
Sam
  • 3,067
  • 19
  • 53
  • 55
  • 1
    You probably meant `MsgBox aWS.UsedRange.Columns.Count` (Column with an `s` at the end) - same for rows. – assylias Sep 15 '12 at 18:26
  • Ok, thank you very much assylias. Also I wasn't sure what UsedRange.Row does. Or UsedRange.Column. They show from doing a few tests now the **first** populated row/column number from the sheet. – Sam Sep 15 '12 at 18:37
  • Yes that's what it does: http://msdn.microsoft.com/en-us/library/office/ff196952.aspx – assylias Sep 15 '12 at 18:44
  • @assylias, your comment should be posted as a solution. :) – Reafidy Sep 15 '12 at 22:13

1 Answers1

1

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.

assylias
  • 321,522
  • 82
  • 660
  • 783
  • +1, @Sam, also see my post here: [used range](http://stackoverflow.com/questions/7423022/excel-getting-the-actual-usedrange/7423252#7423252) – Reafidy Sep 15 '12 at 22:22