VBA scripts (existing) copy and paste fromula down

gazmoz17

Board Regular
Joined
Sep 18, 2020
Messages
146
Office Version
  1. 365
Platform
  1. Windows
Hi,

How do I ammend the below please, so the formula is not applied to cells not showing due to not meeting current filter criteria on cells.

Blag scenario:

e.g. have an existing column of data in Col B. Column has a filter on for even numbers etc, run my macro and it currently copies formula down for all cells existing in column B. Including odd numbers hidden by filter. How do I ammend please so only applies to even numbers/filter conditions?

VBA Code:
Sub Macro2()
'
' Keyboard Shortcut: Ctrl+K
'
Dim a As Long
a = ActiveCell.SpecialCells(xlLastCell).Row
Selection.AutoFill Destination:=Range(Cells(ActiveCell.Row, ActiveCell.Column).Address & ":" & _
Cells(a, ActiveCell.Column).Address)
End Sub

Many Thanks
Gareth
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
Hi Gareth,

AutoFill should have problems filling anything into a non consecutive range. Maybe try a different way and see if the code does what you want:
Excel Formula:
Sub MrE1217353_2()
'https://www.mrexcel.com/board/threads/vba-scripts-existing-copy-and-paste-fromula-down.1217353/
Dim rngVisible As Range
Const cstrCOL As String = "A" 'Column with continuous data throughout, please change accordingly, Character or Number of Column

If ActiveCell.Column <> 2 Then Exit Sub   'only work on Column 2 or B, please change accordingly
If ActiveCell.Row < 2 Then Exit Sub     'assuming that row 1 are the headers, change 2 to number of first data row if needed
If ActiveCell.Value = vbNullString Then Exit Sub    'cell needs to have some content

Set rngVisible = Range(ActiveCell, Cells(Cells(Rows.Count, cstrCOL).End(xlUp).Row, ActiveCell.Column)).SpecialCells(xlCellTypeVisible)
rngVisible.FormulaR1C1 = ActiveCell.FormulaR1C1

Set rngVisible = Nothing
End Sub
Ciao,
Holger
 
Upvote 0
Hi Holger,

Thanks for that works great per those column references 👍. However, is there a way to make it dynamic irrespective of column refs etc as I would use this on multiple work books.

Many Thanks
Gareth
 
Upvote 0
Hi Gareth,

depends on what you have in mind when asking for dynamic. If you want to let it work on any column you could omitt the check for the column to work on. Being dynamic means that even the column of consecutive data should be free. The first idea when reading the request was to call a procedure which gets the information via parameters so that it can be called manually as well as by events. The call might be changed, different calls may be implemented, the procedure in a standard module will stay the same:
VBA Code:
Sub CallFillVisibleCellsOnAutoFilter()

'only work if Autofilter is applied
If ActiveSheet.AutoFilterMode Then
  'Parameters for consecutive data in Column "A", Column 2 or "B" to be worked on, Data starts in Row 2
  'switch this call as needed
  FillVisibleCellsOnAutoFilter "A", 2, 2
Else
  MsgBox "No Autofilter applied, no copies to make", , "Nothing to do here"
End If

End Sub

Sub FillVisibleCellsOnAutoFilter(strColCont As String, _
                                  lngColCopy As Long, _
                                  lngRowStart As Long)
'Parameters used:
'   strColCont    the column which is filled throughout with data, reference for the data
'   lngColCopy    number of column to work on for filling on the values or formulas
'   lngRowStart   row on which data starts in order to exclude the headers
Dim rngVisible As Range

If ActiveCell.Column <> lngColCopy Then Exit Sub   'only work on Column 2 or B, please change accordingly
If ActiveCell.Row < lngRowStart Then Exit Sub     'assuming that row 1 are the headers, change 2 to number of first data row if needed
If ActiveCell.Value = vbNullString Then Exit Sub    'cell needs to have some content

Set rngVisible = Range(ActiveCell, Cells(Cells(Rows.Count, strColCont).End(xlUp).Row, ActiveCell.Column)).SpecialCells(xlCellTypeVisible)
rngVisible.FormulaR1C1 = ActiveCell.FormulaR1C1

Set rngVisible = Nothing

End Sub
And the code above will only work from the current row to the end. If you choose any other than the first visible cell, every visible cell above will stay untouched. If you want the whole range to be changed accordingly instead of using
VBA Code:
Set rngVisible = Range(ActiveCell, Cells(Cells(Rows.Count, strColCont).End(xlUp).Row, ActiveCell.Column)).SpecialCells(xlCellTypeVisible)
change the code to read
VBA Code:
Set rngVisible = Range(Cells(lngRowStart, lngColCopy), Cells(Cells(Rows.Count, strColCont).End(xlUp).Row, ActiveCell.Column)).SpecialCells(xlCellTypeVisible)
This code will always perform on the currently active sheet in the active workbook.

Ciao,
Holger
 
Upvote 0
Hi Holger,

Thanks for this much appreciated. Will need to put some time aside tomorrow morning to get my head around it and report back 😂. Have a more pressing question to ask the forum now as its slowing an overall procedure down (prob relating to hardrive crashing and trying to recover/run existing vba script).

Many Thanks
Gareth
 
Upvote 0

Forum statistics

Threads
1,213,524
Messages
6,114,117
Members
448,549
Latest member
brianhfield

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top