Reduce a Selection 2

[Finality]

Hello,

Excel question: The following code does what I need except the select includes a bottom cell that has text. I need to select a range and hide it leaving a row between data sets.

Example: Column A, Rows 1-3 have data, Rows 4-6 are empty, Rows 7-10 have data. This code selects rows 4-7, I just need rows 4 and 5.

Help!

Sub SelectandHideRowsUnused()
'Begins by setting static point "A5" then finding next blank down.
Range("a5&quot.Select
ActiveCell.Offset(1, 0).Select
Do While Not IsEmpty(ActiveCell)
ActiveCell.Offset(1, 0).Select
Loop

Set TopCell = Cells(1, ActiveCell.Column)
Set BottomCell = Cells(130, ActiveCell.Column)

On Error Resume Next
If IsEmpty(ActiveCell.Offset(-1, 0)) Then Set TopCell = ActiveCell.End(xlUp) Else Set TopCell = ActiveCell
If IsEmpty(ActiveCell.Offset(1, 0)) Then Set BottomCell = ActiveCell.End(xlDown) Else Set BottomCell = ActiveCell
Range(TopCell, BottomCell).Offset(0, 0).Select

End Sub

 

[Zathras]

This routine will hide rows where there are two or more blank cells in column A on consecutive rows.

If you need more sophisticated test than just looking for blank/non-blank in Column A, post back and this can be modified:
[blue]

Sub LeaveOneBlankRow()
Dim c As Range
  For Each c In Intersect(ActiveSheet.UsedRange, Columns(1))
    If IsEmpty(c) And IsEmpty(c.Offset(1, 0)) Then
      c.EntireRow.Hidden = True
    End If
  Next c
End Sub

[/color]

 

[Loomah]

Hi
Not 100% sure what you're after here! When I tried your code it selected rows 3 to 6!!

Anyway, as a starter, this will select rows 4 & 5 as per your requirement. It will actually select all the empty rows below your data minus the last empty row. If there is only one row it will be selected. It assumes there is another block of data following in after the first empty rows.

Sub a()
Dim lRow As Long
Dim lRow2 As Long
lRow = Cells(1, 1).End(xlDown).Row + 1
If IsEmpty(Cells(lRow + 1, 1)) Then
    lRow2 = Cells(lRow, 1).End(xlDown).Row - 2
Else
    lRow2 = lRow
End If
Range(Cells(lRow, 1), Cells(lRow2, 1)).Select
End Sub

;-)

If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244

 

[Finality]

Thank you gentlemen, I am working with both examples now.

 

[Finality]

Zathras, your code works very well. Thank you! Star!

 

[TonyJollans]

Hi Finality,

I'm with Loomah - your code doesn't do what you say, but if you just want to select the first block of blank cells in column A then a single statement will do it:

Columns("A:A").SpecialCells(xlCellTypeBlanks).Areas(1).Select

This can then be built on to do whatever you please next.

Enjoy,
Tony