Help with Macro to select dynamic range

    Hello,


    I'm working on a workbook that required some automation that I managed to include using macros by recording and from comments in this forum. I'm newb to coding, so all I can really do is connect pieces of code that make some kinda of sense to me :saint::|


    Context


    My data always begins on U9 and always ends on column AS but the number of rows is variable from 1 to 400


    In all cells from U9 to AS400 I have formula that hides text until conditions are met, example: @U9 =if(u8<>"";"Banana";""), so some rows are blank (="") but not exactly empty


    = The number of rows that will be filled with actual information is variable and depends on other formulae, but if U9 has Banana, then all cells (U9:AS9) will have some value


    What I haven't been able to do and need help ^^


    Bottomline is I need the macro to be able to select all rows that contain data from columns U to AS -- kind of like select all data until there's a blank row


    Example 1: 300 rows have data, range is U9:AS308

    Example 2: 29 rows have data, range is U9:AS37


    My data is continuous so if the data stop at row 37, rows 38-408 are always blank


    Then I need the macro to select and copy the range of these non-blank cells from U to AS


    I hope it's clear and that you can help me!


    Thanks!!!

    Hi & welcome to the board.

    How about

    Code
    Sub tmyflyte()
   Dim UsdRws As Long
   
   UsdRws = Range("U:U").Find("*", , xlValues, , xlByRows, xlPrevious, , , False).Row
   If UsdRws > 9 Then Range("U9:AS" & UsdRws).Copy
End Sub

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login