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.