gmaxey
08-07-2019, 06:10 AM
Hi Gurus,
I'm fairly sure the answer to this question is already here to be discovered, but I just don't know enough about Excel to know it if I saw it. Sorry.
I have data arranged in single columns separated by an empty column. The columns with data have varying amounts of data. Some may use 8 rows, some 10 etc.
Additionally some columns may have blank cells e.g., cell 3 in a column with data in cells 1, 2, 4-10.
I am trying to get the data form each used column using the following code which works well when there is complete unbroken (no empty cell) in column. I realized that .CurrentRegion is the wrong choice.
Sub ScratchMacro()
'A basic Word macro coded by Greg Maxey
Dim oRng
Dim varIndProps
For Each oRng In Sheets(2).UsedRange.Rows(1).SpecialCells(2).Areas
varIndProps = oRng.CurrentRegion.Resize(, 1) 'This works if there is constant uninterupted data from the first row to last in the column.
'However is there is an empty cell, say in the third row, and all other rows have data then only the data in the first two
'cells are returned.
'??? So to account for these cases, instead of using "CurrentRegion" how can I define the range from the first cell to the
'last used cell in the range?
Next
lbl_Exit:
Exit Sub
End Sub
Thanks.
I'm fairly sure the answer to this question is already here to be discovered, but I just don't know enough about Excel to know it if I saw it. Sorry.
I have data arranged in single columns separated by an empty column. The columns with data have varying amounts of data. Some may use 8 rows, some 10 etc.
Additionally some columns may have blank cells e.g., cell 3 in a column with data in cells 1, 2, 4-10.
I am trying to get the data form each used column using the following code which works well when there is complete unbroken (no empty cell) in column. I realized that .CurrentRegion is the wrong choice.
Sub ScratchMacro()
'A basic Word macro coded by Greg Maxey
Dim oRng
Dim varIndProps
For Each oRng In Sheets(2).UsedRange.Rows(1).SpecialCells(2).Areas
varIndProps = oRng.CurrentRegion.Resize(, 1) 'This works if there is constant uninterupted data from the first row to last in the column.
'However is there is an empty cell, say in the third row, and all other rows have data then only the data in the first two
'cells are returned.
'??? So to account for these cases, instead of using "CurrentRegion" how can I define the range from the first cell to the
'last used cell in the range?
Next
lbl_Exit:
Exit Sub
End Sub
Thanks.