Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > autofill to visible cells only
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 10
Default autofill to visible cells only
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
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 9,101
Default autofill to visible cells only

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
Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 10
Default autofill to visible cells only
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
Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 10
Default autofill to visible cells only
Hello again,

After further 'tweaking', my new issue is that the second and subsequent use
of:

.Range("D2").Copy _
Destination:=DestCells

....causes the "6" and "12" to not be copied down. If I comment this out the
code proceeds but does not autofill down the 6 or 12. I will have two more
subsequent filters & autofills for "24" and "36" so I only need it to
function til then.

How can I get this section to recognize the new cell to be filled down? For
each filter it will be a different and random value/location. (Data sets will
change each time the code is run, appearance and location of the 3, 6, 12,
24, and 26 values may or may not occurence in each data set and will begin in
different rows.)

I've tried changing ".Range("D2")." to ".ActiveCell.", ".UsedCell.",
".LastCell.", ".Range.", and ".Selection."
--
Jeannell


"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
Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 9,101
Default 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
Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Copy Visible Cells in Sheet with Merged and Hidden Cells rtwiss Excel Discussion (Misc queries) 5 April 25th 23 09:08 AM
I can't autofill cells in a column if blank cells in between SJ Excel Worksheet Functions 1 May 4th 08 01:27 AM
Autofill: Need to autofill one week block, (5) weekday only into cells. dstock Excel Discussion (Misc queries) 1 June 17th 05 08:21 PM
AutoFill Visible Cells with Months Elaine New Users to Excel 3 March 16th 05 10:13 PM
Help: Copying Visible Cells only to Visible cells! Jay Jayakumar Excel Programming 0 July 9th 03 08:25 PM


All times are GMT +1. The time now is 07:05 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement