autofill to visible cells only
I found a few potential problems with the code below. I don't think you are
copying the rows with the data that you want. See comments below
'-----------------------------
'no sheet specified
Rows("1:1").Select
'-------------------------------
Selection.AutoFilter
ActiveSheet.Range("$A$1:$CR$25587").AutoFilter Field:=5, Criteria1:="0"
ActiveSheet.Range("$A$1:$CR$25587").AutoFilter Field:=8, Criteria1:="0"
Range("D1").Select
Do
ActiveCell.Offset(1, 0).Select
If ActiveCell.EntireRow.Hidden = False Then
Exit Do
End If
Loop
ActiveCell.FormulaR1C1 = "3"
'-------------------------------
'this does nothing
ActiveCell.Copy
'--------------------------------
'****INSERT FIND LAST ROW and copy formula down
Dim LastRow As Long
Dim LastCol As Long
Dim rng As Range
With ActiveSheet
Set rng = .UsedRange 'try to reset lastused cell
With rng
LastRow = .Rows(.Rows.Count).Row
LastCol = .Columns(.Columns.Count).Column
End With
Set DestCells = .Range("D2:D" & LastRow) _
.SpecialCells(xlCellTypeVisible)
.Range("D2").Copy _
Destination:=DestCells
End With
'****END INSERT FIND LAST ROW
ActiveSheet.ShowAllData
'-------------------------------
'there is no sheet specified
Rows("1:1").Select
'------------------------------
ActiveSheet.Range("$A$1:$CR$25587").AutoFilter Field:=6, Criteria1:="0"
ActiveSheet.Range("$A$1:$CR$25587").AutoFilter Field:=9, Criteria1:="0"
'------------------------------
'no shet specified
Range("D7").Select
'------------------------------
Do
ActiveCell.Offset(1, 0).Select
If ActiveCell.EntireRow.Hidden = False Then
Exit Do
End If
Loop
ActiveCell.FormulaR1C1 = "6"
'-----------------------------------
'does nothing
ActiveCell.Copy
'-----------------------------------
'****INSERT FIND LAST ROW and copy formula down
With ActiveSheet
Set rng = .UsedRange 'try to reset lastused cell
With rng
LastRow = .Rows(.Rows.Count).Row
LastCol = .Columns(.Columns.Count).Column
End With
Set DestCells = .Range("D2:D" & LastRow).SpecialCells(xlCellTypeVisible)
.Range("D2").Copy _
Destination:=DestCells
End With
'****END INSERT FIND LAST ROW
ActiveSheet.ShowAllData
'-----------------------------------
'no sheet specifed
Rows("1:1").Select
'------------------------------------
"jsmith" wrote:
Joel,
Thank you very much! That (of course) worked well.
However, when I string it together with subsequent filters/autofills, they
seem to retain the value "3" even though I have specified new values to
autofill.
Rows("1:1").Select
Selection.AutoFilter
ActiveSheet.Range("$A$1:$CR$25587").AutoFilter Field:=5, Criteria1:="0"
ActiveSheet.Range("$A$1:$CR$25587").AutoFilter Field:=8, Criteria1:="0"
Range("D1").Select
Do
ActiveCell.Offset(1, 0).Select
If ActiveCell.EntireRow.Hidden = False Then
Exit Do
End If
Loop
ActiveCell.FormulaR1C1 = "3"
ActiveCell.Copy
'****INSERT FIND LAST ROW and copy formula down
Dim LastRow As Long
Dim LastCol As Long
Dim rng As Range
With ActiveSheet
Set rng = .UsedRange 'try to reset lastused cell
With rng
LastRow = .Rows(.Rows.Count).Row
LastCol = .Columns(.Columns.Count).Column
End With
Set DestCells = .Range("D2:D" & LastRow).SpecialCells(xlCellTypeVisible)
.Range("D2").Copy _
Destination:=DestCells
End With
'****END INSERT FIND LAST ROW
ActiveSheet.ShowAllData
Rows("1:1").Select
ActiveSheet.Range("$A$1:$CR$25587").AutoFilter Field:=6, Criteria1:="0"
ActiveSheet.Range("$A$1:$CR$25587").AutoFilter Field:=9, Criteria1:="0"
Range("D7").Select
Do
ActiveCell.Offset(1, 0).Select
If ActiveCell.EntireRow.Hidden = False Then
Exit Do
End If
Loop
ActiveCell.FormulaR1C1 = "6"
ActiveCell.Copy
'****INSERT FIND LAST ROW and copy formula down
With ActiveSheet
Set rng = .UsedRange 'try to reset lastused cell
With rng
LastRow = .Rows(.Rows.Count).Row
LastCol = .Columns(.Columns.Count).Column
End With
Set DestCells = .Range("D2:D" & LastRow).SpecialCells(xlCellTypeVisible)
.Range("D2").Copy _
Destination:=DestCells
End With
'****END INSERT FIND LAST ROW
ActiveSheet.ShowAllData
Rows("1:1").Select
' Selection.AutoFilter
ActiveSheet.Range("$A$1:$CR$25587").AutoFilter Field:=7, Criteria1:="0"
ActiveSheet.Range("$A$1:$CR$25587").AutoFilter Field:=10, Criteria1:="0"
Range("D7").Select
Do
ActiveCell.Offset(1, 0).Select
If ActiveCell.EntireRow.Hidden = False Then
Exit Do
End If
Loop
ActiveCell.FormulaR1C1 = "12"
ActiveCell.Copy
'****INSERT FIND LAST ROW and copy formula down
With ActiveSheet
Set rng = .UsedRange 'try to reset lastused cell
With rng
LastRow = .Rows(.Rows.Count).Row
LastCol = .Columns(.Columns.Count).Column
End With
Set DestCells = .Range("D2:D" & LastRow).SpecialCells(xlCellTypeVisible)
.Range("D2").Copy _
Destination:=DestCells
End With
'****END INSERT FIND LAST ROW
Range("A1").Select
ActiveSheet.ShowAllData
End Sub
--
Thanks again!
Jeannell
"Joel" wrote:
Dim LastRow As Long
Dim LastCol As Long
Dim rng As Range
With ActiveSheet
Set rng = .UsedRange 'try to reset lastused cell
With rng
LastRow = .Rows(.Rows.Count).Row
LastCol = .Columns(.Columns.Count).Column
End With
Set DestCells = .Range("D2:D" &
LastRow).SpecialCells(xlCellTypeVisible)
.Range("D2").Copy _
Destination:=DestCells
End With
'
"jsmith" wrote:
Hello,
Any suggestions on how I can make this code ONLY copy down (autofill)
visible cells only? I do not want the value "3" copied in to hidden cells
(rows).
I've searched solutions and cannot find this specific issue.
Rows("1:1").Select
Selection.AutoFilter
ActiveSheet.Range("$A$1:$CR$25587").AutoFilter Field:=5, Criteria1:="0"
ActiveSheet.Range("$A$1:$CR$25587").AutoFilter Field:=8, Criteria1:="0"
Range("D1").Select
Do
ActiveCell.Offset(1, 0).Select
If ActiveCell.EntireRow.Hidden = False Then
Exit Do
End If
Loop
ActiveCell.FormulaR1C1 = "3"
ActiveCell.Copy
'****INSERT FIND LAST ROW and copy formula down
Dim LastRow As Long
Dim LastCol As Long
Dim rng As Range
With ActiveSheet
Set rng = .UsedRange 'try to reset lastused cell
With rng
LastRow = .Rows(.Rows.Count).Row
LastCol = .Columns(.Columns.Count).Column
End With
.Range("D2").AutoFill _
Destination:=.Range("D2:D" & LastRow), Type:=xlFillDefault
End With
'****END INSERT FIND LAST ROW
Thanks in advance--
Jeannell
|