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?