import cell value ( area ) to VBA.

Hello.

i'm looking to get my macro to select an area based on 2 cells values.

for examlple:
i want my macro to look at I3 & I4 and then select the area described in these cells, so if the contents in I3 & I4 is A1 and C46 then the macro will select A1:46.

see picture for reference.

snippet of current code:

Code:

---

    Dim Source As Range
    Dim Dest As Workbook
    Dim wb As Workbook
    Dim TempFilePath As String
    Dim TempFileName As String
    Dim FileExtStr As String
    Dim FileFormatNum As Long
    Dim OutApp As Object
    Dim OutMail As Object
    Set Source = Nothing
    On Error Resume Next
    Set Source = Range("want macro to set area specified in I3 and I4 here, if possibe").SpecialCells(xlCellTypeVisible)
    On Error GoTo 0

    If Source Is Nothing Then
        MsgBox "The source is not a range or the sheet is protected, please correct and try again.", vbOKOnly
        Exit Sub
    End If
    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With
    Set wb = ActiveWorkbook
    Set Dest = Workbooks.Add(xlWBATWorksheet)

---

Code:

---

Set Source = Range(Range("I3").Value, Range("I4").Value).SpecialCells(xlCellTypeVisible)

---

Artik

The source is not a range or the sheet is protected, please correct and try agein.

i forgot to mention.
the cells to call and get value from is on "Sheet2"
sorry for the hassel.

So, you have active eg Sheet1 and now you want to get the values of I3 and I4 cells from Sheet2 and create a range reference in Sheet2. If so, then:

Code:

---

  With Worksheets("Sheet2")
  Set Source = .Range(.Range("I3").Value, .Range("I4").Value).SpecialCells(xlCellTypeVisible)
  End With

---

Pay attention to the dots before Range.

Artik

Sorry, agein i misrepresented my wish :(
range reference in sheet1

sheet1 - the area to be selected
sheet2 (I3 & I4 = the range to select on sheet1 )

sorry for the misunderstanding

In that case

Code:

---

  With Worksheets("Sheet2")
    Set Source = Worksheets("Sheet1").Range(.Range("I3").Value, .Range("I4").Value).SpecialCells(xlCellTypeVisible)
  End With

---

Gets values from Sheet2 and creates a range reference in Sheet1.

Artik