Excel macro to select variable range of rows and columns

Question

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 :X (see code below)

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 actual data, then all cells on that row will have actual values (e.g. valid range then is U9:AS9)

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 that I will paste somewhere else

My code now

This worked fine until today when I realized that I used 46 rows of info to record the macro (I think it's the 'Application.Goto Reference:="R9C21:R55C45"' part of the code), which then only selects 46 rows.

What I did was:

Used a formula to write the range I needed in a cell, for example: cell H5 has text 'U9:AS10', so while recording I copied that cell, opened the Go To dialog, pasted that information and voi'la, the range I needed was selected. But then I realized it always selected that range, regardless of the 'range' updated on cell H5 (kind of like the recording ignored the copy/paste steps idk)

Sub Exportar()
Application.DisplayAlerts = False
Application.ScreenUpdating = False

Dim strSourceSheet As String
Dim strFullname As String
Set objWS = CreateObject("WScript.Shell")
strDesktopPath = objWS.SpecialFolders("Desktop")

    Sheets("Exportar").Select
    Cells.Select
    Selection.ClearContents
    Range("A1").Select
    Sheets("Produtos para cadastrar").Select
    Range("H5").Select
    Selection.Copy
    Application.Goto Reference:="R9C21:R55C45"
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Exportar").Select
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Columns("J:K").Select
    Selection.Replace What:=",", Replacement:=".", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Range("A1").Select
    Sheets("Produtos para cadastrar").Select
    Application.CutCopyMode = False
    Range("D2").Select

'~~> Change the below two lines as per your requirements
strSourceSheet = "Exportar"

ThisWorkbook.Sheets(strSourceSheet).Copy
ActiveWorkbook.SaveAs strDesktopPath & "\" & "produtos_novos_" & Format(Date, "yyyymmdd"), FileFormat:= _
                      xlCSV, CreateBackup:=False
ActiveWorkbook.Close

Application.DisplayAlerts = True
MsgBox (" Arquivo exportado com sucesso!  ")
End Sub

tl;dr can't code, need help to make a macro to automatically select range of cells that always starts at cell U9 and ends at column AS but with variable number of rows excluding blank cells?

I hope it's clear! Any help is appreciated!

Tks

What is the name of your source sheet? and destination sheet? — Wils Mils
Source is 'Produtos para cadastrar', destination is 'Exportar' — tmyflyte

Wils Mils Wils Mils · Accepted Answer · 2020-07-25T19:26:48

My effort to understand the problem. Please see my comments for explanation.

Sub Exportar()
Dim strSourceSheet As String
Dim strDesktopPath As String
Dim shExportar As Worksheet
Dim shProdutos As Worksheet
Dim lastRow As Long 
Dim currentRow As Long

Application.DisplayAlerts = False
Application.ScreenUpdating = False

strSourceSheet = "Exportar"
Set shExportar = ThisWorkbook.Sheets(strSourceSheet)
Set shProdutos = ThisWorkbook.Sheets("Produtos para cadastrar")

' find the first blank row starting at U9
currentRow = 9

' loop through each row until it finds blank
Do Until shProdutos.Range("U" & currentRow).Value = ""
    currentRow = currentRow + 1
Loop

' last non-blank cell is above current row
lastRow = currentRow - 1

' clear contents of destination sheet
shExportar.Cells.ClearContents

' copy data from U9 to AS[last row]
shProdutos.Range("U9:AS" & lastRow).Copy

' paste values(excluding formula) to destination sheet
shExportar.Range("A1").PasteSpecial Paste:=xlPasteValues

' I just rearranged the code below to make sense
Set objWS = CreateObject("WScript.Shell")
strDesktopPath = objWS.SpecialFolders("Desktop")

ThisWorkbook.Sheets(strSourceSheet).Copy

ActiveWorkbook.SaveAs strDesktopPath & "\" & "produtos_novos_" & Format(Date, "yyyymmdd"), FileFormat:= _
                      xlCSV, CreateBackup:=False

Application.DisplayAlerts = True
MsgBox (" Arquivo exportado com sucesso!  ")

ActiveWorkbook.Close

End Sub

Excel macro to select variable range of rows and columns

tl;dr can't code, need help to make a macro to automatically select range of cells that always starts at cell U9 and ends at column AS but with variable number of rows excluding blank cells?

1 Answers