index range till last indexed column

[Afarag]

hello there,

please i need help in modify the below code plus selection the range till last indexed row i need to select the range till last indexed column not to ("Q")

Dim rng As Range, OutApp As Object, OutMail As Object, lr%
Dim lastColumn As Integer
On Error GoTo 0
Sheets("Data").Activate
lr = Evaluate("=max((B11:B100<>"""")*(row(B11:B100)))") 'last non blank cell
Set rng = Sheets("Data").Range("B6:Q" & lr)

'MsgBox rng.Address, 64, "this range will be exported to Outlook"
If rng Is Nothing Then
    MsgBox "The selection is not a range or the sheet is protected" & _
          vbNewLine & "please correct and try again.", vbOKOnly
    Exit Sub

gratefully,

 

[Chihiro]

Something like below.

Dim rng As Range, OutApp As Object, OutMail As Object, lr%
Dim lAddress As String
On Error GoTo 0
Sheets("Data").Activate
lAddress = Range("A1").SpecialCells(xlCellTypeLastCell).Address
Set rng = Sheets("Data").Range("B6:" & lAddress)

'MsgBox rng.Address, 64, "this range will be exported to Outlook"
If rng Is Nothing Then
    MsgBox "The selection is not a range or the sheet is protected" & _
          vbNewLine & "please correct and try again.", vbOKOnly
    Exit Sub

 

[Afarag]

Hello Chihiro,

unfortunately the above code didn't get the indexed range only, in additional to it omitted the option of index till last row.

Gratefully,

 

[Chihiro]

Can you upload sample data set?

 

[Afarag]

you can download it from the below link
https://drive.google.com/file/d/0BwxxQXMJl4FLX2FFQUNmOC1hMW8/view?usp=sharing

although there is no a huge amount of data but it sized 6m !!!,
as soon as you open the workbook at "Mail" sheet, hit the mail image to popup an userform alternate between "Daily" or "Monthly" button to change the filtered range then hit the userform mail icon.

thanks a lot,

 

[Chihiro]

Try this.

Dim rng As Range, lCell As String, OutApp As Object, OutMail As Object, lr As Long, lc As Long
On Error GoTo 0
Sheets("Data").Activate
lr = Evaluate("=max((B11:B100<>"""")*(ROW(B11:B100)))") 'last non blank cell
lc = Evaluate("=max((B11:Q11<>"""")*(COLUMN(B11:Q11)))")
lCell = Sheets("Data").Cells(lr, lc).Address
Set rng = Sheets("Data").Range("B6:" & lCell)
'MsgBox rng.Address, 64, "this range will be exported to Outlook"
If rng Is Nothing Then
    MsgBox "The selection is not a range or the sheet is protected" & _
          vbNewLine & "please correct and try again.", vbOKOnly
    Exit Sub
End If

 

[Afarag]

Hello Chihiro,

the last updated code works fine,
thanks a lot for your support