How to use a subset of a range?

[Cat]

Hello.

I would like to search the data which reside on a specific column of a
worksheet. The problem is, that the data don't start from the first
row. I created a simplified vba for illustration purposes. The code
draws a box on the region I want to search. PLEASE NOTE THAT THE CODE
IS NOT THE ACTUAL CODE, but a simplified demo code.

Sub Button1_Click()
Const dataColumn As String = "C"
Const dataStarts As Integer = 4
Dim allData As Range
Dim sheet As Worksheet

Set sheet = ActiveSheet
Set allData = sheet.Columns(dataColumn)
Set allData = allData.Offset(dataStarts, 0) <===== RUN ERROR 1004!
Without this line, it will search from the first row.
allData.Cells.BorderAround XlLineStyle.xlContinuous
End Sub

The error message is "Application-defined or object-defined error" and
the help in Excel wasn't much helpful. Basically, I want to start
searching from the "dataStarts" row not from the first row.

What is wrong with my code? Thank you for any hints.

 

[Harald Staff]

You are almost there.
Problem is that you set the whole column as range and then try to shift it 4
rows down. The sheet does not have any more rows and can't do this.

Demo with a smaller range, c1:C1000, and you see what happens:

Sub Button1_Click()
Const dataColumn As String = "C"
Const dataStarts As Integer = 4
Dim allData As Range
Dim sheet As Worksheet

Set sheet = ActiveSheet
Set allData = sheet.Range("C1:C1000")
Set allData = allData.Offset(dataStarts, 0)
MsgBox allData.Address
End Sub

HTH. Best wishes Harald

 

[Don Guillett Excel MVP]

You are almost there.
Problem is that you set the whole column as range and then try to shift it 4
rows down.  The sheet does not have any more rows and can't do this.

Demo with a smaller range, c1:C1000, and you see what happens:

Sub Button1_Click()
    Const dataColumn As String = "C"
    Const dataStarts As Integer = 4
    Dim allData As Range
    Dim sheet As Worksheet

    Set sheet = ActiveSheet
    Set allData = sheet.Range("C1:C1000")
    Set allData = allData.Offset(dataStarts, 0)
    MsgBox allData.Address
End Sub

HTH. Best wishes Harald











- Show quoted text -

You may ? like a more simplifiied version
Sub BordersStartRowToLastRow()
Dim sr As Long
sr = 4
ActiveSheet.Cells(sr, "c").Resize(Columns("c").Rows.Count - sr) _
..BorderAround XlLineStyle.xlContinuous
End Sub
Sub BordersStartRowToLastUSEDRow()
Dim sr As Long
Dim lr As Long
sr = 4
lr = Cells(Rows.Count, 3).End(xlUp).Row
ActiveSheet.Cells(sr, "c").Resize(lr - sr + 1) _
..BorderAround XlLineStyle.xlContinuous
End Sub