Find Last Row and Column

A

alexaronson

Active Member
Joined
Sep 30, 2005
Messages
314
Hello,

I have some code that I want to beef up. One of the things I want to do is get rid of some hard references.

For Example,
Code: 
Range("B1:H381").Select
How can I make the above code find H381 without me typing it in?

Thanks in advance for any help!
-Alex in Memphis, TN
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
Sort by date Sort by votes
What is in H381 that it is looking for?
Or are you just looking for the last row in column H with data in it?
 
Upvote 0
If you are looking for the last row in column H with data, here is a calculation to return that:

MyLastRow = Range("H" & Rows.Count).End(xlUp).Row

So then you could use:

Range("B1:H" & MyLastRow).Select

Though, you may be able to speed up your code if you can avoid using Select statements (many times it is not necessary).
 
Upvote 0
Joe4,

Thank you for your help.
I have a follow up question for you.

Now that I can identify my last row, I need to identify a certain number of rows above my last row for a range that I want to copy. I have tried the following code in my test sheet but I keep running into a problem with the Range statement. Do you have any suggestions?

Also, can you elaborate by what you mean about the Select statements. Are there other ways to select a range of cells to copy more efficiently? I am interested in learning more since my end program will be processing up to 2.4 Million data points daily when I am done.

Thanks,


Code: 
Sub LastRowMacro()
Dim MyLastRow As String, MyFirstRow As String
Sheets("Sheet1").Select
MyLastRow = Range("A" & Rows.Count).End(xlUp).Row
MyFirstRow = MyLastRow - 5
MyFirstRow = "A" & MyFirstRow & ":"
MyLastRow = "A" & MyLastRow
Range(MyFirstRow & MyLastRow).Select.Copy
Range("G9").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
End Sub
 
Upvote 0
Using a lot of Select statements is often unnecessary and slows down your code.

You can shorten this:
Code: 
Range(MyFirstRow & MyLastRow).Select.Copy
Range("G9").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
to this:
Code: 
Range(MyFirstRow & MyLastRow).Copy
Range("G9").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
As you can see, we got rid of three Select/Selections.

In defining ranges, you can also use the Cell method to indiate a certain cell. The format is:
Cell(Row, Column)

So I can re-write your code to this:
Code: 
Sub LastRowMacro()
    Dim MyLastRow As Long
    Dim MyFirstRow As Long
Sheets("Sheet1").Select
MyLastRow = Range("A" & Rows.Count).End(xlUp).Row
MyFirstRow = MyLastRow - 5
Range(Cells(MyFirstRow, "A"), Cells(MyLastRow, "A")).Copy
Range("G9").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
End Sub
BTW, I think your original problem was that you were trying to use MyFirstRow and MyLastRow as both numbers (first to get the row number) and then string (to define the alphanumeric range reference). You typically do not want to use one variable as a number and then change it to a string.
 
Upvote 0
how can I use this macro to select the last row valid data and paste that number to an especific cell in the same sheet??
 
Upvote 0
Are you wanting to copy and paste the whole row or just certain cells?

If you can lay out the details, we can help you with it. Most of what you need may already be contained in this thread already.
 
Upvote 0
Joe.... thanks for your answer.
I would like to get the number from last row and paste it in an specific cell. Using the info in this thread this is the macro:

Sub LastRowMacro()
Sheets("Sheet1").Select
MyLastRow = Range("M" & Rows.Count).End(xlUp).Row.Copy
Selection.Paste Destination:=ActiveSheet.Range("C9")
End Sub

I will like the macro to be applied to all sheets; the location of the cells will be the same.

let me know your comments...
 
Upvote 0
You must log in or register to reply here.

Similar threads

T
  • Solved
finding the last used column when it is merged?
Replies
7
Views
175
jolivanes
jolivanes
K
  • Solved
last occurrence of a value in a 4 column array
Replies
4
Views
95
kmr47
K
S
  • Question
Go to cell in very last column of the active row
Replies
2
Views
209
Fluff
Fluff
J
  • Solved
VBA To paste in the last column after data
Replies
5
Views
113
jondavis1987
J
C
  • Solved
Trouble with vba 'loop' finding last row
Replies
5
Views
331
CaptainGravyBum
C

Forum statistics

Threads
1,214,838
Messages
6,121,885
Members
449,057
Latest member
Moo4247

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top