VBA Macro code for Ctrl+Shift+Up? (code from macro recording not working correctly)

[Mavericx]

Hi,

Having an issue with a recorded macro (Im a newbie) and hoping a kind soul here can help me out.

Trying to trim up a selection, and wanting to use (the equivalent of) ctrl+shift+up to trim the selection to the last cell with data. Using this in excel works perfectly, but when running the macro, it doesn't work correctly - it just highlights the cell above the existing selection as well, instead of trimming the lower selection up to the last cell with data.

Here is the code that was generated when recording the macro (the "bold" code is what doesn't seem to be working correctly):

Range("L41").Select
Application.Goto Reference:="R41C12:R100C12"
Range(Selection, Selection.End(xlUp)).Select
Application.CutCopyMode = False
Selection.Copy

Any thoughts/ideas on how to fix this would be very very much appreciated!

And if more info is needed, please just let me know.

Thx so much!

 

[MARK858]

If you mean from L41 to the last cell with data in column L then maybe...

Range("L41:L" & Range("L" & Rows.count).End(xlUp).Row).Copy

 

[Mavericx]

Mark!! You beautiful bastard....that worked PERFECTLY!!!!

And my goodness, that is so much cleaner then what was generated during the macro recording. haha

I can't tell you how much I appreciate this...I was pulling my hair out in frustration. Thank you so much!!!

 

[Mavericx]

Just to clarify..."beautiful bastard" is meant as a term of endearment. Don't want that to be misconstrued/misinterpreted!

 

[MARK858]

You're welcome

 

[Mavericx]

Hey Mark, a quick follow-up if you don't mind...

I applied the formula to other columns as well with (of course) the same great effect, except for one situation where it isn't working quite correctly for one specific column.

This specific column is for "shipping address line 2" (ex -apartment number, unit number, etc), so sometimes there might not be any data at all. In this scenario, it actually highlighted and copied the cell above (AL40), which I don't want to happen. (see picture below)

And again, this is just for this one specific column where there is a problem. I assume I would maybe need some kind of if/then code or something like that, where it check to make sure there's data...

Any thoughts on how I can address this?

Again, thank you so much Mark!

 

[MARK858]

    Dim lr As Long
    lr = Range("L" & Rows.Count).End(xlUp).Row
   
    If lr < 42 Then
        Exit Sub
        'or
        'Range("L41").Select
    Else
        Range("L41:L" &  lr).Copy
    End If

 

[Mavericx]

Hey Mark, I tried the code, but got an error (see pic). I tried adding a ")" (see 2nd pic), but still no bueno...

I'm so sorry man...I know you have better things to do than mess around with this. If you don't want to answer any longer, I will completely understand!

I'm already very grateful for the help provided. Thx again!

 

[MARK858]

My fault, editing freehand, just

Range("AL41:AL" & lr).Copy

 

[Mavericx]

Hey Mark,

Hope you had a good weekend!

I just wanted to (finally) check back in and let you know that the change worked great!

Thank again man, you absolutely ROCK!!!