xldown is giving me an error

[TButhe]

I am trying to use xldown to select a range of cells and clear the contents. I have recorded the code and it turns out exactly the same. However, when I try to run it I get an error "Compile error: Expected function or variable" (if it is expected then why is it an error??? ) and the xldown is highlighted as the error. What could be wrong? I have used this and am using it in another macro and it works fine. Is it just something in this macro that I have wrong?

Sub Copy3()
' Copy3 Macro
' Macro recorded 12/15/2004 by Tracy Buthe
ActiveSheet.Unprotect
    Columns("R:R").Select
    Selection.ClearContents
    Columns("P:P").Select
    Selection.Copy
    Columns("R:R").Select
    ActiveSheet.Paste
    Columns("N:N").Select
    Selection.Copy
    Columns("P:P").Select
    ActiveSheet.Paste
    Columns("L:L").Select
    Selection.Copy
    Columns("N:N").Select
    ActiveSheet.Paste
    Columns("J:J").Select
    Selection.Copy
    Columns("L:L").Select
    ActiveSheet.Paste
    Columns("H:H").Select
    Selection.Copy
    Columns("J:J").Select
    ActiveSheet.Paste
    Columns("F:F").Select
    Selection.Copy
    Columns("H:H").Select
    ActiveSheet.Paste
    Range("F4") = InputBox("Enter the last day of the month you are working  with, such as 3/31/04")
    Columns("F:F").Select
    Selection.NumberFormat = "General"
    Range("F4").Select
    Selection.NumberFormat = "[$-409]mmm-yy;@"
    Columns("F:F").Select
    Selection.Locked = False
    Selection.FormulaHidden = False
    Range("F1:F4").Select
    Selection.Locked = True
    Selection.FormulaHidden = False
    Range("F5").Select
    Selection.End(xldown).Select
    Selection.ClearContents
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
        , AllowInsertingRows:=True, AllowDeletingRows:=True, AllowFiltering:=True _
        , AllowUsingPivotTables:=True, AllowSorting:=True
End Sub

Thanks!
TJ

acsmile:

[mvidas]

Hi TJ,

I'm not sure why that wouldn't be working, it looks like you want to clear the last used cell below F5 before hitting any blanks.

Here is your same code, a bit cleaner and no cells are selected so it will run a bit faster as well:

Sub Copy3()
    ActiveSheet.Unprotect
    Columns("R").ClearContents
    Columns("P").Copy Columns("R")
    Columns("N").Copy Columns("P")
    Columns("L").Copy Columns("N")
    Columns("J").Copy Columns("L")
    Columns("H").Copy Columns("J")
    Columns("F").Copy Columns("H")
    Range("F4") = InputBox("Enter the last day of the month you are working with, such as 3/31/04")
    Columns("F:F").NumberFormat = "General"
    Range("F4").NumberFormat = "[$-409]mmm-yy;@"
    Columns("F").Locked = False
    Columns("F").FormulaHidden = False
    Range("F1:F4").Locked = True
    Range("F5").End(xlDown).ClearContents
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
    , AllowInsertingRows:=True, AllowDeletingRows:=True, AllowFiltering:=True _
    , AllowUsingPivotTables:=True, AllowSorting:=True
End Sub

I noticed that your "xldown" is lowercase for some reason, perhaps you may want to try to substitute the value for xldown instead?

Range("F5").End(-4121).ClearContents

I don't know why you'd get that error with xldown being highlighted. Can you post a sample workbook perhaps?

Matt

[TButhe]

cleaned up code. I don't know if I will every learn this stuff but it is fun trying. I still got the error though when I tried your first solution.

Range("F5").End(-4121).ClearContents

doesn't give me an error but it doesn't delete the cell contents. I've attached the file.

BTW - I'm using Excel XP and Windows 2000

[mvidas]

TJ,

OK sorry about the delay, had a lot of work to do, then a few other questions as well.

I figured out what was wrong, this has been driving me nuts for a while now!

In your Module4, you have a sub named xldown. If you change the name to this, you'll get rid of the error.

I don't know why the -4121 line didnt delete the contents, but I've got a question. Are you trying to delete from F5 through F5.end(xldown) or just the last one? The way your macro is coded, it will only clear the contents of the single cell that is reached when xldown is done. If you're trying to clear the entire range, use the line:
[vba]Range("F5", Range("F5").End(xlDown)).ClearContents[/vba]

Chances are, thats why you're not getting your desired results. You may also want to consider using

Range("F5", Range("F65536").End(xlup)).ClearContents

Just in case you have a blank cell in the range. Say you wanted to delete F5:F100, but for some reason F60 was blank. The line with xlDown will only delete F5:F59, rather than everything in F from F5 and below. The modified line (with xlup) will do just that.

I hope I haven't confused you, please let me know if you need any help or any more clarification!
Matt

[Jacob Hilderbrand]

You have a sub callled xldown in Module4. That is causing a conflict. Rename that sub. You can then clear the range like this:

Range("F5", Range("F5").End(xldown)).ClearContents

[Ken Puls]

Hi TButhe,

Just curious to know if your issues have been solved...?

[TButhe]

DUH!!!!!!! Thanks everyone!! Let's marked this solved and a lesson well learned. WATCH WHAT YOU NAME YOUR MACROS!!!