Jump to content
Lydia

ANSWERED cambiar rango hasta la última celda con datos

Recommended Posts

Hola a todos!!

He grabado la siguiente macro pero  cuando quiero cambiar la sentencia me da error.

"Selection.AutoFill Destination:=Range("K2:K631256")
    Range("K2:K631256").Select

Selection.AutoFill Destination:=Range("K2").End(xlDow)
    Range("K2").End(xlDow).Select

 

Os pongo la macro entera a continuación. 

Muchas gracias de antemano por la ayuda.

 

Sub AÑADIRFORMULASFUENTEDEDATOS()
'
    Columns("J:J").Select
    Selection.TextToColumns Destination:=Range("J1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
        :=".", FieldInfo:=Array(Array(1, 2), Array(2, 1)), TrailingMinusNumbers:=True
    Columns("K:K").Select
    Selection.Delete Shift:=xlToLeft
    Range("K1").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 255
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    ActiveCell.FormulaR1C1 = "NUM_MES"
    Range("J3").Select
    Columns("K:K").ColumnWidth = 42.57
    Range("K2").Select
    ActiveCell.FormulaR1C1 = _
        "=VLOOKUP(CONCATENATE(YEAR(RC[-2]),""_"",MONTH(RC[-2])),AUX!R1C1:R25C2,2,FALSE)"
    Range("K2").Select
    Selection.AutoFill Destination:=Range("K2:K631256")
    Range("K2:K631256").Select
    Columns("K:K").ColumnWidth = 24.43
    Range("L1").Select
    Columns("L:L").ColumnWidth = 23.43
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 255
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    ActiveCell.FormulaR1C1 = "DÍA DE LA SEMANA"
    Range("L2").Select
    ActiveCell.FormulaR1C1 = "=WEEKDAY(RC[-3],11)"
    Range("L2").Select
    Selection.AutoFill Destination:=Range("L2:L631256")
    Range("L2:L631256").Select
    Range("M1").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 255
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    ActiveCell.FormulaR1C1 = "HORA"
    Range("M2").Select
    ActiveCell.FormulaR1C1 = "=HOUR(RC[-3])"
    Range("M2").Select
    Selection.AutoFill Destination:=Range("M2:M631256")
    Range("M2:M631256").Select
    ActiveWindow.SmallScroll Down:=-15
    Range("K2:M2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    ActiveWindow.SmallScroll Down:=-60
    ActiveWindow.ScrollRow = 631224
    ActiveWindow.ScrollRow = 211054
    ActiveWindow.ScrollRow = 122954
    ActiveWindow.ScrollRow = 3874
    ActiveWindow.ScrollRow = 2
    ActiveWindow.SmallScroll Down:=-6
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
End Sub
 

 

 

Share this post


Link to post
Share on other sites

Hola Antoni!

Muchas gracias por tu ayuda, pero me sigue saliendo el mismo error "error definido por la aplicación o el objeto"

Adjunto el fichero de prueba por si le sirve de mejor guía.

Muchas gracias de nuevo

PRUEBA.xlsm

Share this post


Link to post
Share on other sites

Selection.AutoFill Destination:=Range(Range("K2"), Range("K2").End(xlDown))

Range(Range("K2"), Range("K2").End(xlDown)).Select

Revisa toda la macro por si hubiera más instrucciones con este error.

Si no te funciona, comentas.

Edited by Antoni

Share this post


Link to post
Share on other sites
Guest
This topic is now closed to further replies.

×
×
  • Create New...

Important Information

Privacy Policy

Ayuda Excel - Madrid, Madrid, ES - Valorada por 6254 personas - Aprender Excel - Total: 4.7 / 5