Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy subset of Range | Excel Programming | |||
Defining a range as a subset of cells in another range | Excel Programming | |||
Trying to set a pivot field to a subset of values listed in range of cells | Excel Programming | |||
How to enter symbols for subset or element of a subset in Excel? | Excel Worksheet Functions | |||
create a subset range in a VBA UDF | Excel Programming |