How to use a subset of a range?

  • Thread starter Thread starter Cat
  • Start date Start date
C

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.
 
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 -
Click to expand...

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