Get range till last indexed cell

[Afarag]

Dears,

i have a dynamic range based on userform buttons, "Daily" & "Monthly", need when hit "Monthly" need to select range Sheets("Data").Range("B6:J11"), but when hitting "daily" need to select the range till last indexed cell to send it via outlook, the problem that i faced is the range based on "Monthly" only that activated

Private Sub Mail_Click()
    Dim rng As Range
    Dim OutApp As Object
    Dim OutMail As Object
        Dim rngColU As Range
    Dim rngLastNonBlank As Range
    Set rng = Nothing
    MthlyDailyIdentifier = "Daily"
    MthlyDailyIdentifier = "Monthly"
    On Error Resume Next
    'Only the visible cells in the selection
    'Set rng = Selection.SpecialCells(xlCellTypeVisible)
    'You can also use a fixed range if you want
            If MthlyDailyIdentifier = "Monthly" Then
        Set rng = Sheets("Data").Range("B6:J11").SpecialCells(xlCellTypeVisible)
    ElseIf MthlyDailyIdentifier = "Daily" Then
      With Worksheets("Data")
        'Assign used range of column U to a range variable.
        'rngColU will include cells with formulas that return blanks.
       
        Set rngColU = .Range(.Cells(1, "L"), .Cells(.Rows.Count, "L").End(xlUp))
    End With
   
    'Find the last non blank cell in rngColU
    With rngColU
        Set rngLastNonBlank = .Find(What:="*", After:=.Cells(1, 1), _
                            LookIn:=xlValues, _
                            LookAt:=xlWhole, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlPrevious, _
                            MatchCase:=False)
    End With
   
    If Not rngLastNonBlank Is Nothing Then
'        MsgBox "Last non blank cell in column L is " & rngLastNonBlank.Address(0, 0)
    End If
'    set rng to the alternative range here for Monthly Identifier.


 
End If


    On Error GoTo 0

 

[wudixin96]

 MthlyDailyIdentifier = "Daily"
    MthlyDailyIdentifier = "Monthly"

The value of MthlyDailyIdentifier is always equal to "Monthly"

 

[Afarag]

Monthly & Daily is a button which alternate a data in a listbox and i poppedup this retrieved data to table to be able sending via Outlook, in Private Sub Daily_Click(), i inserted MthlyDailyIdentifier = "Daily". and in Private Sub Daily_Click(), i inserted MthlyDailyIdentifier = "Monthly"

i need when click monthly, select a range ("B6:J11"). but in clicking daily, need to select the range till last indexed cell in table ("B6:Last indexed cell")

gratefully,

 

[wudixin96]

How about this ??

Set rngColU = .Range("B6:" & .UsedRange.SpecialCells(xlCellTypeLastCell).Address(0, 0))

 

[Afarag]

the problem still found the range of "Monthy" selection is that copied to mail body