error 1004 autofilter method of range class failed

[ExcelDennis]

Hi All,

Using 3 lines of code below i run into error 1004 autofilter method of range class failed on the last line.

Range("C12:P12").Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.AutoFill Destination:=Range("C12:P12"), Type:=xlFillWeekdays

Could someone please help me to make these 3 lines of code work.

Many thanks in advance

Regards, Dennis

 

[ExcelDennis]

Glad you sorted it & thanks for the feedback

Dear Sir,

Today with testing the code I run into an error

Sub Orgineel()
Dim Ar As Areas
Dim i As Long

Set Ar = Range("C12:AI12").SpecialCells(xlVisible).Areas
For i = Ar.Count To 1 Step -1
With Ar(i).Offset(, Ar(i).Count - 1).Resize(, 1)
If .Value <> "" Then
.AutoFill Ar(i), xlFillWeekdays
Else
.Value = Application.WorkDay(Ar(i + 1).Resize(, 1), -1)
If Ar(i).Count > 1 Then .AutoFill Ar(i), xlFillWeekdays
End If
End With
Next i
End Sub

This is the code, it works great only not when the first cel left from

AI12 is hidden, or the first two cells left from AI12 are hidden

Can u please help me to fix this error.

thanks again.

Regards, Dennis
​

 

[Fluff]

Ok, how about

Sub ExcelDennis()
   Dim AR As Areas
   Dim i As Long
   
   Set AR = Range("C12:AI12").SpecialCells(xlVisible).Areas
   For i = AR.Count To 1 Step -1
      With AR(i).Offset(, AR(i).Count - 1).Resize(, 1)
         If AR(i).Count > 1 And .Value <> "" Then
            .AutoFill AR(i), xlFillWeekdays
         ElseIf .Value = "" Then
            .Value = Application.WorkDay(AR(i + 1).Resize(, 1), -1)
            If AR(i).Count > 1 Then .AutoFill AR(i), xlFillWeekdays
         End If
      End With
   Next i
End Sub

 

[ExcelDennis]

Ok, how about

Sub ExcelDennis()
   Dim AR As Areas
   Dim i As Long
   
   Set AR = Range("C12:AI12").SpecialCells(xlVisible).Areas
   For i = AR.Count To 1 Step -1
      With AR(i).Offset(, AR(i).Count - 1).Resize(, 1)
         If AR(i).Count > 1 And .Value <> "" Then
            .AutoFill AR(i), xlFillWeekdays
         ElseIf .Value = "" Then
            .Value = Application.WorkDay(AR(i + 1).Resize(, 1), -1)
            If AR(i).Count > 1 Then .AutoFill AR(i), xlFillWeekdays
         End If
      End With
   Next i
End Sub

Works perfect.

Could u tell me also what the code would be for the other way around.

for example for a range ("AI12:BA12")

Thanks again

Regards, Dennis

 

[Fluff]

Maybe (untested)

Sub ExcelDennis()
   Dim AR As Areas
   Dim i As Long
   
   Set AR = Range("AI12:BA12").SpecialCells(xlVisible).Areas
   For i = 1 To AR.Count
      With AR(i).Resize(, 1)
         If AR(i).Count > 1 And .Value <> "" Then
            .AutoFill AR(i), xlFillWeekdays
         ElseIf .Value = "" Then
            .Value = Application.WorkDay(AR(i - 1).Resize(, 1), 1)
            If AR(i).Count > 1 Then .AutoFill AR(i), xlFillWeekdays
         End If
      End With
   Next i
End Sub

 

[ExcelDennis]

Maybe (untested)

Sub ExcelDennis()
   Dim AR As Areas
   Dim i As Long
   
   Set AR = Range("AI12:BA12").SpecialCells(xlVisible).Areas
   For i = 1 To AR.Count
      With AR(i).Resize(, 1)
         If AR(i).Count > 1 And .Value <> "" Then
            .AutoFill AR(i), xlFillWeekdays
         ElseIf .Value = "" Then
            .Value = Application.WorkDay(AR(i - 1).Resize(, 1), 1)
            If AR(i).Count > 1 Then .AutoFill AR(i), xlFillWeekdays
         End If
      End With
   Next i
End Sub

Thanks,

Almost what I am looking for

Result of your Macro is

3-9-2019 | 4-9-2019 | 5-9-2019 | 6-9-2019 | 9-9-2019 | 4-9-2019 | 5-9-2019 | 6-9-2019 | 9-9-2019 | 10-9-2019 |

5-9-2019 | 6-9-2019 | 9-9-2019 |

Should be

3-9-2019 | 4-9-2019 | 5-9-2019 | 6-9-2019 | 9-9-2019 | 10-9-2019 | 11-9-2019 | 12-9-2019 | 13-9-2019 | 16-9-2019 | 17-9-2019 | 18-9-2019 | 19-9-2019 |
Will u please take another look. Thanks​

 

[Fluff]

How about

Sub ExcelDennis()
   Dim AR As Areas
   Dim i As Long
   
   Set AR = Range("AI12:BA12").SpecialCells(xlVisible).Areas
   For i = 1 To AR.Count
      With AR(i).Resize(, 1)
         If AR(i).Count > 1 And .Value <> "" Then
            .AutoFill AR(i), xlFillWeekdays
         ElseIf .Value = "" Then
            .Value = Application.WorkDay(AR(i - 1).Offset(, AR(i - 1).Count - 1).Resize(, 1), 1)
            If AR(i).Count > 1 Then .AutoFill AR(i), xlFillWeekdays
         End If
      End With
   Next i
End Sub