Setting Range in VBA

[Niraj Baraili]

Hi Experts,

Need your help on this...

I have range like this in column C

C1 Data
C2 45
C3 55
C4 65
C5 48
C6 49
C7 51
C8 52
C9 69

Sub Test
  Dim LRow As Integer
  LRow = Sheet1.Cells(Rows.Count, "C").End(xlUp).Row


  With Sheet1
  Set myrange = .Range(cells(2,3), Cells(LRow, 3))
  End With

End

Now if c2, c3 is blank, myrange shoud be like this Range(cells(4,3), Cells(LRow, 3)).

Any input on this greatly appreciated.

 

[Debaser]

Here is one way:

    Dim LRow                  As Long
    Dim firstRow              As Long
    Dim MyRange               As Range

    With Sheet1
        LRow = .Cells(.Rows.Count, "C").End(xlUp).Row
        If Len(.Cells(2, 3).Value2) <> 0 Then
            firstRow = 2
        Else
            firstRow = .Cells(1, 3).End(xlDown).Row
        End If

        Set MyRange = .Range(.Cells(firstRow, 3), .Cells(LRow, 3))
    End With
    MsgBox MyRange.Address

Note: always use Long for a row number variable. Integer can only cope up to 32767.

 

[Niraj Baraili]

Thank you very much Debaser.

 

[Hui]

or even simpler:

Sub Test()

  Dim LRow As Integer
  LRow = Sheet1.Cells(Rows.Count, "C").End(xlUp).Row
  Set myRange = Range(Cells(LRow, 3), Cells(LRow, 3).End(xlUp))
  Debug.Print myRange.Address
End Sub

 

[Debaser]

If there are no gaps within the data... Also, that could include A1 if there were no blanks.

 

[shrivallabha]

With VBA, you can write in many ways to get the same result in many cases.
e.g.

Sub VBAOneMoreWay()
Dim myRange As Range
Set myRange = Range(IIf(Len(Range("C2").Value) = 0, Range("C2").End(xlDown), Range("C2")), Range("C" & Rows.Count).End(xlUp))
MsgBox myRange.Address
End Sub

 

[Niraj Baraili]

Thanks Shrivallabha.