Find Last Row and Column

[alexaronson]

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,

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

 

[Joe4]

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?

 

[Joe4]

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).

 

[alexaronson]

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,

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

 

[Joe4]

Using a lot of Select statements is often unnecessary and slows down your code.

You can shorten this:

Range(MyFirstRow & MyLastRow).Select.Copy
Range("G9").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

to this:

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:

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.

 

[mrmd]

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??

 

[Joe4]

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.

 

[mrmd]

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...

 

[Joe4]

Check out this link to see how you can loop through all sheets in VBA code:

http://www.mrexcel.com/forum/showthread.php?t=366887&highlight=loop+sheets