How can I fix my autofilter?

[roxnoxsox]

I think my error might be with the selected range to copy maybe..? I'm trying to check if cell in column D is empty and if so, then copy the contents of cell in column C and paste into D. I'm doing this for a very large amount of data. Currently I'm trying to do this through an autofilter (open to other suggestions if anyone knows of a better way!) so that the visible contents of column C are pasted into D. But it's coming up with an error message when I run the macro: (the line beginning 'Range("D2:D" & LR.....' is the line highlighted in Debug)

    LR = Cells(Rows.Count, 1).End(xlUp).Row
    Rows("1:1").Select
    Selection.AutoFilter
    ActiveSheet.Range("D2:D" & LR).AutoFilter Field:=4, Criteria1:="="
    Range("C2", Range("C2").End(xlDown)).Select
    Selection.Copy
    Range("D2:D" & LR).SpecialCells(xlCellTypeVisible).PasteSpecial xlValues
    Rows("1:1").Select
    Application.CutCopyMode = False
    ActiveSheet.ShowAllData
    Selection.AutoFilter
    Range("A1").Select

Is this an obvious error? ;_; My range of data will change a lot so I need this to be able to run for however many rows there are which is where the LR comes in.. Thanks in advance!

[Bob Phillips]

This works

Dim LR As Long
Dim rngArea As Range
    LR = Cells(Rows.Count, 1).End(xlUp).Row
    Rows("1:1").AutoFilter
    ActiveSheet.Range("D2:D" & LR).AutoFilter Field:=4, Criteria1:="="
    For Each rngArea In Range("C2:C" & LR).SpecialCells(xlCellTypeVisible).Areas
    
        rngArea.Copy
        rngArea.Offset(0, 1).PasteSpecial xlValues
    Next rngArea
    ActiveSheet.ShowAllData
    Rows("1:1").AutoFilter
    Range("A1").Select

[roxnoxsox]

Perfect, this worked great! Many thanks

[snb]

wouldn't this ?

Sub M_snb()
    [D1:D2000] = [if(D1:D2000="",C1:C2000,D1:D2000)]
End Sub

[Aussiebear]

True snb, but what if the range was bigger ( given the the OP suggested that the range had to be for how ever many rows) in post #1

[snb]

What about changing 2000 ? no big deal I gather.

but alternatively:

Sub M_snb() 
  usedrange.columns(4).name="snb"
  usedrange.columns(3).name="snb1"
  [snb] = [if(snb="",snb1,snb)] 
End Sub

[mancubus]

@snb
since 'SNB1' is a cell address just change it to, eg, snbxx.

[snb]

Good idea; snb0 would do as well.