VBA fill right

[qvinh]

This code works the issue I have is it takes a very long time for it to execute due to the large data. If I were to do this manually then I would apply my filter criteria then go to column F select the first cell of filtered data then control shift down then drag that selection over the Column G. The data changes daily so one day the filtered data may begin in cell F1590 next day may be F1580 etc. Any advice would be appreciated.
Sub Scrubbing()
Dim LastRow As Long
Dim FilteredDataRange As Range
Dim Cell As Range

' Disable screen updating to improve performance
Application.ScreenUpdating = False

' Find the last row in column A (assuming it contains the data)
LastRow = Cells(Rows.Count, 1).End(xlUp).Row

' Apply the filter criteria to column AB (Field 24)
ActiveSheet.Range("$A$1:$AB$" & LastRow).AutoFilter Field:=24, Criteria1:="=No", Operator:=xlOr, Criteria2:="="

' Apply the filter criteria to column G (Field 7)
ActiveSheet.Range("$A$1:$AB$" & LastRow).AutoFilter Field:=7, Criteria1:=Array( _
"ALL SUPERVISORS", "IN-PLANT SUPPORT", "MAIN DISTRIBUTION TOUR-I", _
"MAIN DISTRIBUTION TOUR-II", "MAIN DISTRIBUTION TOUR-III", _
"MAINTENANCE OPERATIONS"), Operator:=xlFilterValues

' Find the first cell with filtered data in column F (excluding header)
Set FilteredDataRange = Columns("F").SpecialCells(xlCellTypeVisible)

If Not FilteredDataRange Is Nothing Then
' Loop through each visible cell in column F
For Each Cell In FilteredDataRange
' Fill right in column G for the current cell in column F
Cell.Offset(0, 1).Resize(1, Cell.MergeArea.Cells.Count).Value = Cell.Value
Next Cell
End If

' Clear the filters
ActiveSheet.AutoFilterMode = False

' Re-enable screen updating
Application.ScreenUpdating = True
End Sub
[/CODE]

 

[Herakles]

This code works the issue I have is it takes a very long time for it to execute due to the large data. If I were to do this manually then I would apply my filter criteria then go to column F select the first cell of filtered data then control shift down then drag that selection over the Column G. The data changes daily so one day the filtered data may begin in cell F1590 next day may be F1580 etc. Any advice would be appreciated.
Sub Scrubbing()
Dim LastRow As Long
Dim FilteredDataRange As Range
Dim Cell As Range

' Disable screen updating to improve performance
Application.ScreenUpdating = False

' Find the last row in column A (assuming it contains the data)
LastRow = Cells(Rows.Count, 1).End(xlUp).Row

' Apply the filter criteria to column AB (Field 24)
ActiveSheet.Range("$A$1:$AB$" & LastRow).AutoFilter Field:=24, Criteria1:="=No", Operator:=xlOr, Criteria2:="="

' Apply the filter criteria to column G (Field 7)
ActiveSheet.Range("$A$1:$AB$" & LastRow).AutoFilter Field:=7, Criteria1:=Array( _
"ALL SUPERVISORS", "IN-PLANT SUPPORT", "MAIN DISTRIBUTION TOUR-I", _
"MAIN DISTRIBUTION TOUR-II", "MAIN DISTRIBUTION TOUR-III", _
"MAINTENANCE OPERATIONS"), Operator:=xlFilterValues

' Find the first cell with filtered data in column F (excluding header)
Set FilteredDataRange = Columns("F").SpecialCells(xlCellTypeVisible)

If Not FilteredDataRange Is Nothing Then
' Loop through each visible cell in column F
For Each Cell In FilteredDataRange
' Fill right in column G for the current cell in column F
Cell.Offset(0, 1).Resize(1, Cell.MergeArea.Cells.Count).Value = Cell.Value
Next Cell
End If

' Clear the filters
ActiveSheet.AutoFilterMode = False

' Re-enable screen updating
Application.ScreenUpdating = True
End Sub
[/CODE]

Can you please use code tags to submit your code. Use the VBA option on the ribbon?

Can you also please submit a good representatitve example of your data using XL2BB?

An explanation of what you are trying to achieve rather than people having to work this out from your code would be appreciated.

 

[Peter_SSs]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Excel VBA with fill right
There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.