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

 

[Fluff]

You will need to tell us what you are trying to do.
The error you have reported does not fit with those lines of code.

 

[ExcelDennis]

You will need to tell us what you are trying to do.
The error you have reported does not fit with those lines of code.

I have a date in cell P12. I drag the date to cell C12. In the range "P12:C12 two columns are hidden. (column H and column I

I want the drag to skip the hidden columns -> Selection.SpecialCells(xlCellTypeVisible).Select

The range can only contain weekdays -> Selection.AutoFill Destination:=Range("C12:P12"), Type:=xlFillWeekdays

So the result is

Column C Column D Column E Column F Column G Column J Column K Column L Column M Column N Column O Column P
16-8-2019 | 19-8-2019 | 20-8-2019 | 21-8-2019 | 22-8-2019 | 23-8-2019 | 26-8-2019 | 27-8-2019 | 28-8-2019 | 29-8-2019 | 30-8-2019 | 02-09-2019

Hope this is more clear.
Regards and many thanks for your help

 

[Fluff]

In that case try

Sub ExcelDennis()
   Range("P12").AutoFill Range("j12:P12"), xlFillWeekdays
   Range("G12").Value = Application.WorkDay(Range("J12"), -1)
   Range("G12").AutoFill Range("C12:G12"), xlFillWeekdays
End Sub

 

[ExcelDennis]

In that case try

Sub ExcelDennis()
   Range("P12").AutoFill Range("j12:P12"), xlFillWeekdays
   Range("G12").Value = Application.WorkDay(Range("J12"), -1)
   Range("G12").AutoFill Range("C12:G12"), xlFillWeekdays
End Sub

Thanks it works. But the columns that are hidden are not always the same in the range.

So do u have a way to fix this.

Thanks again. Regards, Dennis

 

[Fluff]

How about

Sub ExcelDennis()
   Dim Ar As Areas
   Dim i As Long
   
   Set Ar = Range("C12:P12").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

 

[ExcelDennis]

How about

Sub ExcelDennis()
   Dim Ar As Areas
   Dim i As Long
   
   Set Ar = Range("C12:P12").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

Thanks this is working

But is it possible to go one step further. what if I want this part of code

Range("C12:P12")

be replaced by
Range("Q12").Select
Range(Selection, Selection.End(xlToLeft)).Select because the size of the range could be different everytime.

Thanks again.<sub><sup><strike>



</strike></sup></sub>​

 

[Fluff]

Maybe it would be a good idea for you to explain EXACTLY what you are after.
I am not a mind reader

 

[ExcelDennis]

Maybe it would be a good idea for you to explain EXACTLY what you are after.
I am not a mind reader

Hi,

Figured that last question out myself. Many thanks for your great help.

I am not sure how to close this topic, but it can be closed.

Best regards,
Dennis

 

[Fluff]

Glad you sorted it & thanks for the feedback