How to use a subset of a range?
 

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.

How to use a subset of a range?
 

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



"Cat" wrote in message
...
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.

How to use a subset of a range?
 

On Aug 13, 3:36*am, "Harald Staff" wrote:
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

"Cat" wrote in message

...



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.- Hide quoted text -

- 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