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


    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!


  • Hi & welcome to the board.

    How about

    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!