VBA: Range setup last used range number

I used the following

The following  Range("Sheet1!A2:A" & Rows.Count) To define a range from the first cell to the last cell.

The problem is that Row.Count don't take the LastUsedRange of the specific Range.

How should I define my Range by keeping the same strcture Range("Sheet1!A1:A" & Row.Count (but row.Count of the specific column)

Thank you very much.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.


pls try

Range("Sheet1!A2:A" & Sheets("Sheet1").UsedRange.Rows.Count)

Open in new window

Bill PrewIT / Software Engineering ConsultantCommented:
Try this:

Range("Sheet1!A1:A" & Sheet1.Cells(Sheet1.Rows.Count, "A").End(xlUp).Row))


Range("Sheet1!A2:A" & Range("Sheet1!A" & Rows.Count).End(xlUp).Row)
Introduction to R

R is considered the predominant language for data scientist and statisticians. Learn how to use R for your own data science projects.

LD16Author Commented:
Thank you very much.

And if you are working with two different workbook is it possible to define the workbook before the range

wb1 = Workbooks.Open("filepath")
wb2 = Workbooks.Open("filepath)

can I proceed like this:

wb1.Range("Sheet1!A2:A" & Range("Sheet1!A" & Rows.Count).End(xlUp).Row)
wb2.Range("Sheet1!A2:A" & Range("Sheet1!A" & Rows.Count).End(xlUp).Row)
LD16Author Commented:

Concerning your formula:
Bill Prew:

Range("Sheet1!A1:A" & Sheet1.Cells(Sheet1.Rows.Count, "A").End(xlUp).Row))

I see that your second Sheet1 is not defined with "" that means that you take the Sheet1 name as it is defined in the Microsoft excel object mode?

However what happen? if I change the name of "Sheet1" by toto and I change of Sheet from the Microsoft excel object perspective, it would not be the est to use the SheetName?

wb1.Sheets("Sheet1").Range("A2:A" & wb1.Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row)
wb2.Sheets("Sheet1").Range("A2:A" & wb2.Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row)
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
with wb1.sheets("Sheet1")
    set rng = .Range("A2:A" & .Cells(.Rows.Count, 1).end(xlUp).Row)
end with

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
LD16Author Commented:
This will also works?

Range("Sheet1!A2:A" & Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
Yes but you do not determine the Workbook here (CORRECTED)

Range("Sheet1!A2:A" & Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row )
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
no -- Rows.Count needs to be referenced too ... hence why I used With -- 3 things to reference that are all the same thing ~
the probability that the rows.count of the 2 worksheets is different and relevant for the problem is almost equal to zero
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
Rows.Count will get the count on the ActiveSheet.  This may or may not be the same -- therefore, it is a good idea to add the worksheet reference.
LD16Author Commented:
Ok, thank you both for your comment.

The with set up reported in  comment 41282392 can be used for CreateObject("Excel.Application) ?


Set objExcel = CreateObject("Excel.Application")
Set objWB = objExcel.Workbooks.Open(objNewestFile.Path, False, False)
objWs = objWb.Sheets(1)

with objWs
    set rng = .Range("A2:A" & .Cells(.Rows.Count, 1).end(xlUp).Row)
end with

Thank you very much for your help.
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
you're welcome ~ happy to help

... and don't forget to release your object variables when you are done
set objWs  = nothing
if not objWB  is nothing then
   objWB.close false
   set objWB = nothing
end if
if not objExcel  is nothing then
   set objExcel  = nothing
end if

Open in new window

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VB Script

From novice to tech pro — start learning today.