CODEWITHSUNDEEP
excelvba
CJG
CJG

Reputation: 25

Need a "Simple" excel macro to find bottom cell in a column, create a range and copy it

I have a spreadsheet I'm using to compile text that changes all the time.

In column AD, Row 4(AD4) I put the contents of text, and it can have data going 1000 to 4000 rows down. It changes every time, so there is no static range name. I need a macro that

The current version finds the bottom cell correctly, but if I run the macro a 2nd time, with new data, it keeps trying to copy the same range. (I used the Formula Define.Name method, to name the cell, and then selected AB4:LastRow) but it is ALWAYS 3160 whether data goes to row 4000 or not.....

Sub Last_row()
Cells(Application.Rows.Count, 30).End(xlUp).Select
' following lines of code are useless
Range("AB4:AD3160").Select
Range("AD3160").Activate
Selection.Copy
End Sub

Upvotes: 0

Views: 12242

Answers (3)

Edwin A.
Edwin A.

Reputation: 1

I know this question is really old, but I just had a similar situation come up and determined a solution that met my needs and might be helpful to others that stumble across this.

Try using .End(xlDown) instead of .End(xlUp).

I had the same problem as described where I was trying to perform an autofill in column B with VBA based on the list of values in column A. A subsequent run with a shorter list in column A would result in the autofill trying to populate B all the way down to the original last row of column A.

NOTE: xlDown may not work if you have blanks within your range of data.

Something like this:

Sub Last_row()

Dim ws As Worksheet
Dim ADlastrow As Long
Set ws = ActiveSheet

ADlastrow = ws.Range("AD4").End(xlDown).Row
Range("AB4:AD"&ADlastrow).select

Selection.copy
' optional - orig post claimed to want to just have
' text to be copied highlighted, but sample code 
' included the code to copy 
End sub

Upvotes: 0

brettdj
brettdj

Reputation: 55692

Your current code hard-codes the range of interest with Range("AB4:AD3160").Select

This code will define a dynamic range starting from AB4 to the last non-empty cell in column AD

You can then use this range (without selecting) for changing values elsewhere (note that you may not need to actually copy rng1, it is possible to dump these values to a separate range directly without a copy and paste.

Sub Last_row()
Dim rng1 As Range
Set rng1 = Range([ab4], Cells(Rows.Count, 30).End(xlUp))
rng1.Copy
End Sub

Update: Example of how to copy a dynamic sized range from one sheet to another without a copy and paste:

Sub Last_row2()
    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    Dim rng1 As Range

    Set ws1 = Sheets(1)
    Set ws2 = Sheets(2)
    Set rng1 = ws1.Range(ws1.[ab4], ws1.Cells(Rows.Count, 30).End(xlUp))
    ws2.[a1].Resize(rng1.Rows.Count, rng1.Columns.Count).Value = rng1.Value
End Sub

Upvotes: 1

Reafidy
Reafidy

Reputation: 8451

To answer your question directly:

With Sheet1
    .Range("AB4", .Cells(Rows.Count, "AD").End(xlUp)).Copy
End With

Copy to specific location WITHOUT using clipboard:

With Sheet1
    .Range("AB4", .Cells(Rows.Count, "AD").End(xlUp)).Copy Sheet2.[A1]
End With

Copy and exclude formatting:

With Sheet1
    With .Range("AB4", .Cells(Rows.Count, "AD").End(xlUp))
        Sheet2.Cells(1, "A").Resize(.Rows.Count, .Columns.Count).Value = .Value
    End With
End With

Note: Replace all sheet codenames (sheet1, Sheet2) above with your actual sheet codenames.

Upvotes: 3

Related Questions