I need to use the lastrow in my range to create a code

Question

I want to create a button that prints out a range of cells but the range always differs by the number of rows. I thought I could use the Lastrow feature so I do not have the manually tweak the code everytime I need to print. The Range that needs to be printed out is B2:S50 but the row number always changes therefore I thought Lastrow would be useful to save me from manually changing.

I have tried the following code(s):

Sub printproposal()

Sheets("Proposals").Activate


Dim Lastrow As Integer

Lastrow = Range("B" & Rows.Count).End(xlUp).Row
' MsgBox Lastrow


Range(Lastrow, "B"):("S2").Printout



Sheets("Proposals").Range(Cells(Lastrow, "B"), 
 Cells("S2")).printout



End Sub

Try `Range("B" & LastRow & ":S2").Printout` – braX Dec 29 '21 at 19:46 — braX, Dec 29 '21 at 19:46

score 0 · Answer 1 · answered Dec 29 '21 at 19:59

Reference Columns Range

When it is unknown which column has the last cell with a value, you could use the RefColumns function:

Option Explicit

Sub PrintProposal()
    
    Const wsName As String = "Proposals"
    Const Cols As String = "B:S"
    Const fRow As Long = 2

    
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    Dim ws As Worksheet: Set ws = wb.Worksheets(wsName)
    Dim frrg As Range: Set frrg = ws.Rows(fRow).Columns(Cols) ' "B2:S2"
    Dim rg As Range: Set rg = RefColumns(frrg) ' "B2:SLastRow"
    
    rg.PrintOut
    
End Sub

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Purpose:      Creates a reference to the range from the first row of a range
'               ('FirstRowRange') to the row range containing
'               the bottom-most non-empty cell in the row's columns.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Function RefColumns( _
    ByVal FirstRowRange As Range) _
As Range
    If FirstRowRange Is Nothing Then Exit Function
    
    With FirstRowRange.Rows(1)
        Dim lCell As Range
        Set lCell = .Resize(.Worksheet.Rows.Count - .Row + 1) _
            .Find("*", , xlFormulas, , xlByRows, xlPrevious)
        If lCell Is Nothing Then Exit Function ' empty range
        Set RefColumns = .Resize(lCell.Row - .Row + 1)
    End With

End Function

I need to use the lastrow in my range to create a code

1 Answers1

Reference Columns Range