0

I have filtered data in worksheet that is missing information in column D. The missing information already filtered out. I want to fill the formula to visible rows in Column D.

I get the first empty visible cell and correct rows count for only visible rows. However the code does not fill the the formula to all visible cells on the range.

This code only writes to first cell that is myCell. The problem seems to be on the last row of the code -> Range("" & CSt....

The amount of data with all the loops is > 200 0000 rows. Therefore a fast method that is focusing only filtered cells is preferred.

Thanks for your help in advance.

Code:

Sub Makro3()
'
Dim myCell As Range
.
.
.
.dim ... 

              
     Hyvite = WSc.Cells(r, 7)
     WSCust = WSc.Cells(r, 1)
    
    Sheets(WSCust).Select

 Sheets("20200").Select
 ActiveSheet.AutoFilter.Range.Offset(1).SpecialCells(xlCellTypeVisible).Cells(1, 4).Select
Set myCell = ActiveCell

 LastRow = ActiveSheet.AutoFilter.Range.Columns(1).SpecialCells(xlCellTypeVisible).Cells.Count - 1
 
     
  myCell.FormulaR1C1 = _
        "=IFERROR(INDEX('YHT hin'!C,MATCH(RC[-3],'YHT hin'!C[-3],0))+ " & CStr(Hyvite) & ","""")"
    
  
   Range("" & CStr(myCell.Address) & "").AutoFill Destination:=Range(" & CStr(myCell.Address) & ":F" & LastRow)
      
End Sub

Can someone help me out to formulate the last row of the code. I cannot find the answer anywhere.

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
Ade
  • 1
  • 1
  • `Range("" & CStr(myCell.Address) & "").AutoFill` is no valid syntax and you can use `myCell` directly: `myCell.AutoFill` your destination has to look like `Destination:=Range(myCell.Address & ":F" & LastRow)` • You might benefit from reading [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). – Pᴇʜ Nov 15 '22 at 14:17
  • Good point of leaving out the range and good link. I changed the code: myCell.AutoFill Destination:=Range(myCell.Address & ":F" & LastRow). However it does not recognise the filtering and fill in the visible cells. Do you have any ideas of make the code focusing to visible cells? – Ade Nov 15 '22 at 14:43
  • Autofill will always fill all cells in the range (visible or not). You cannot focus it to the filtered cells only. You would have to write your own code to fill the data in each cell looping through the visible cells `SpecialCells(xlCellTypeVisible)` only. – Pᴇʜ Nov 15 '22 at 14:54
  • Thanks for the clarification! I'll do it that way. – Ade Nov 15 '22 at 20:12

0 Answers0