Excel VBA - Dynamic Range size

Question

Im trying to find the range on a separate sheet.

Dim abc As Range
Dim size As Integer
size = Sheets("Misc").Cells(1, Sheets("Misc").Cells(1, 1).Rows.End(xlDown).Count)
abc = Sheets("Misc").Range("A1:A" & size)

Im struggling to get the 'size' to give me the count of the rows correctly. What am I doing wrong?

user4039065 · Accepted Answer · 2015-08-13 17:50:59Z

4

You will need to Set the range.

dim sz as LONG
with Sheets("Misc")
    sz = .Cells(rows.count, 1).End(xlUp).Row
    SET abc = .Range("A1:A" & sz)
end with

The row number should be sufficient. You don't really need a .Count for your purposes. Just find the last populated row by looking from the bottom up.

answered Aug 13, 2015 at 17:50

user4039065

Sign up to request clarification or add additional context in comments.

3 Comments

BruceWayne Over a year ago

In his original size = Sheets("Misc")..., wouldn't he need to add .Row, or since Size was set to Integer, VB will recognize he wants an integer, so return the row? I wouldn't think it would, correct?

user4039065 Over a year ago

The original formula was really only referencing a single cell, not a range of cells so size was only ever going to be one. The .Count should have been moved outside the last bracke, possibly as .Rows.Count. It was being used as the column parameter in the Range.Cells property. Suffice to say that there was a lot going on.

Kayote Over a year ago

Thank you for the clarification @Jeeped.

Collectives™ on Stack Overflow

Excel VBA - Dynamic Range size

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related