Data Region & Auto Complete

[Guest]

This is relating to a question from 1/27/04. I have the same issue. Auto complete is doing its thing, and then when theres a blank row, it stops.
I understand that auto complete only works when it is within a data region, and if there is a blank row, it won't auto complete on the next row of a column.
Isn't there some way to format the sheet so the Data Region extends over a stated area? It's so cumbersome dealing w/ large data bases that need to have blank rows here and there. Any ideas?

 

[Bernie Deitrick]

Carrera,

Nope, you're stuck with the blank rows. An alternative is to use a macro
assigned to a custom commandbar:

Sub FillToMatchColumnOnLeft()
ActiveCell.AutoFill Destination:=Range(ActiveCell, _
ActiveCell.Offset(Cells(65536, ActiveCell.Column - 1) _
.End(xlUp).Row - ActiveCell.Row, 0))
End Sub

Note that it will not skip over the blanks. If you want to do that, then you
would need to add

Dim myCell As Range
For Each myCell In Range(ActiveCell(1, 0), _
ActiveCell.Offset(Cells(65536, ActiveCell.Column - 1) _
.End(xlUp).Row - ActiveCell.Row, -1)). _
SpecialCells(xlCellTypeBlanks)
myCell(1, 2).ClearContents
Next myCell


HTH,
Bernie
MS Excel MVP

This is relating to a question from 1/27/04. I have the same issue. Auto

complete is doing its thing, and then when theres a blank row, it stops.

I understand that auto complete only works when it is within a data

region, and if there is a blank row, it won't auto complete on the next row
of a column.

Isn't there some way to format the sheet so the Data Region extends over a

stated area? It's so cumbersome dealing w/ large data bases that need to
have blank rows here and there. Any ideas?