Last Row Autofill

[aeddipa]

So, I've done last row autofills in VBA plenty of times, but for some reason this is causing an issue today. I understand why, but can't figure out the solution.

I am copying data (in this example there is 3700 rows) and pasting to a new sheet only the VISIBLE data after i filter to the critera: NEW. The NEW data contains only 129 rows of the 3700 and is pasted in this other tab called "New Unauth". However, when I autofill to Last Row, instead of it going to row 129 and ending, it continues to 3700. It is reading that there is data in rows 130 - 3700 even when there isnt. Obviously, my copy job isn't doing its job correctly. Is there any way around this?

Sub Unauth_Ded()
 
Dim LR As Long
LR = Range("B" & Rows.Count).End(xlUp).Row
    Selection.AutoFilter Field:=12, Criteria1:="NEW"
    [B]Selection.SpecialCells(xlCellTypeVisible).Select[/B]
[B]    Selection.Copy[/B]
    Sheets("New Unauth").Select
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Cells.Select
    Cells.EntireColumn.AutoFit
    Columns("A:A").Select
    Application.CutCopyMode = False
    Selection.Insert Shift:=xlToRight
    Range("A2").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[1],'CA Codes'!R1C1:R32C2,2,FALSE)"
    Range("A2").Select
    [B]Selection.AutoFill Destination:=Range("A2:A" & LR)[/B]
    [B]Columns("A:A").EntireColumn.AutoFit[/B]
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "Analyst"
    Range("A1").Select
    With Selection.Interior
        .ColorIndex = 15
        .Pattern = xlSolid
    End With
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
 
    Sheets("New Detail").Select
    ActiveSheet.ShowAllData
End Sub

If anybody could help I would greatly appreciate it! Thanks in advance! I'm sure it's something easy that I am missing.

 

[jim may]

Your problem could be here -- LR is still the original 3000+ rows - Right?


Selection.AutoFill Destination:=Range("A2:A" & LR)

 

[aeddipa]

So you're saying that by remove just the "& LR", it will autofill column A to the last row of actual data?

 

[jim may]

No, what I'm saying it the following line (Line 1) returns the integer 3701 (or the approx)

LR = Range("B" & Rows.Count).End(xlUp).Row

Then later on you have the code line:

Selection.AutoFill Destination:=Range("A2:A" & LR)

The above is setting your destination range to go down to the same row # (3701)..
Is this right? I don't think so... LR needs to be updated to = the 129 (approx)

 

[aeddipa]

Jim,

So I noticed my coding was off slightly and that it should be referencing column A in my "LR =..." statement. However, that does not fix the issue. I do want approximately 129 rows. But I obviously dont want to put A1 through A129 because the next time I run this macro, it may contain 200 rows. I need it to end on the last row of data.

The issue remains in the copying of data from one tab to another. While I am only copying visible cells as shown in my coding, it still picks up the source that there were 3700 rows of data (ever if the last 3569 are blank).

Would I perhaps have some time of Last Row statement that took into account the first blank row or would it not work since its picking something up off the background of the sheet?

Thanks for the input!

 

[jim may]

Just as you have done in Line 2 of your code by using:
LR = Range("B" & Rows.Count).End(xlUp).Row

You need to add another line after:
Selection.Insert Shift:=xlToRight
Range("A2").Select

NLR = Range("A" & Rows.Count).End(xlUp).Row

then use the NLR in your

Selection.AutoFill Destination:=Range("A2:A" & NLR)


I Think!!!
Good Luck