Defining range from First Cell to Last Used Cell

[gmaxey]

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.

[Fluff]

How about
varIndProps = Sheets(2).Range(oRng, Cells(Rows.Count, oRng.Column).End(xlUp)) 'This works if there is constant uninterupted data from the first row to last in the column.

[snb]

Start your macro with:


Sub M_snb()
sheet2.Cells.SpecialCells(4).Delete -4162
End Sub

NB. Do not use sheets(2), because it can refer to several sheets.

[mana]

Sub test()
Dim oCol As Range

For Each oCol In Sheets(2).UsedRange.Columns
If WorksheetFunction.CountA(oCol) Then
With oCol.SpecialCells(2).Areas
MsgBox Range(.Item(1)(1), .Item(.Count)(.Item(.Count).Count)).Address
End With
End If
Next

End Sub

[gmaxey]

Fluff,

Thanks. This seems to work. Have not looked at the other suggestions yet.

[gmaxey]

snb,

That just condenses the content into a list of unbroken content and doesn't address the issue.

Attempt to run a line like:
sheet2.Cells.SpecialCells(4).Delete -4162

Just results in an error. Sheets("Sheet2") works though.

[gmaxey]

mana

the code is being run from word so I don't think (or know how) to use the Worksheet function.

[Fluff]

Fluff,

Thanks. This seems to work. Have not looked at the other suggestions yet.
You're welcome & thanks for the feedback

[mana]

Sub test()
Dim xlApp As Object

Set xlApp = GetObject(, "excel.application")
MsgBox xlApp.WorksheetFunction.CountA(xlApp.activeworkbook.sheets(2).Columns(3))

End Sub

[mana]

>For Each oRng In Sheets(2).UsedRange.Rows(1).SpecialCells(2).Areas


Row of first cell is always top?