• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

index range till last indexed column

Afarag

Member
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")


Code:
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,
 
Something like below.

Code:
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
 
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,
 
Try this.

Code:
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
 
Back
Top