Results 1 to 5 of 5

VBA - Last Row Functionality is finding ~10x as many rows as there should be

Threaded View

LarryLegend VBA - Last Row Functionality... 10-17-2013, 05:52 PM
xladept Re: VBA - Last Row... 10-17-2013, 07:41 PM
LarryLegend Re: VBA - Last Row... 10-18-2013, 09:42 AM
Izandol Re: VBA - Last Row... 10-18-2013, 09:47 AM
LarryLegend Re: VBA - Last Row... 10-18-2013, 10:16 AM
  1. #1
    Registered User
    Join Date
    12-06-2011
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    24

    VBA - Last Row Functionality is finding ~10x as many rows as there should be

    I'm certain there is an easy fix to this, but I can't find it.

    This is a simple conversion tool that converted information from one file into the format necessary to input it into an online store.

    For whatever reason, the last row functionality seems to always thing there are about 10 times as many rows as their really are. I can't figure out why. I have attached the file as well as the VBA

    Sub conversion()
    '
    ' conversion Macro
    '
    Application.ScreenUpdating = False
    Sheets("Input").Select
    Cells.Select
    Selection.Copy
    Sheets("Output").Select
    Cells.Select
    ActiveSheet.Paste
    Columns("AD:AY").Select
    Application.CutCopyMode = False
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Sheets("Formulas").Select
    Range("AD2:AY2").Select
    Selection.Copy
    Sheets("Output").Select
    Range("AD2").Select
    ActiveSheet.Paste
    Dim LR As Long
    LR = ActiveSheet.UsedRange.Rows.Count
    Range("ad2:ay2").AutoFill Destination:=Range("ad2:ay" & LR)
    Columns("AY:AY").ColumnWidth = 60
    Cells.Select
    Range("AX1").Activate
    Cells.EntireRow.AutoFit
    Columns("AY:AY").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Columns("AD:AX").Select
    Range("AX1").Activate
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlToLeft
    Columns("C:C").Select
    Selection.Copy
    Columns("CB:CB").Select
    ActiveSheet.Paste
    Columns("CC:CC").Select
    ActiveSheet.Paste
    Columns("D:D").Select
    Application.CutCopyMode = False
    Selection.Copy
    Columns("CD:CD").Select
    ActiveSheet.Paste
    Columns("H:H").Select
    Application.CutCopyMode = False
    Selection.Copy
    Columns("CE:CE").Select
    ActiveSheet.Paste
    Range("CF2").Select
    ActiveCell.FormulaR1C1 = _
    "=IF(OR(ISBLANK(RC[-77]),RC[-77]=0),"""",ROUND(RC[-77]/0.68,2))"
    Range("cf2").Copy Range("cf2", Range("cf2" & Range("d" & Rows.Count).End(xlUp).Row))
    Columns("AD:AD").Select
    Selection.Copy
    Columns("CH:CH").Select
    ActiveSheet.Paste
    Range("CI2").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "New"
    Range("ci2").Copy Range("ci2", Range("ci2" & Range("d" & Rows.Count).End(xlUp).Row))
    Range("CK2").Select
    ActiveCell.FormulaR1C1 = "Yes"
    Range("ck2").Copy Range("ck2", Range("ck2" & Range("d" & Rows.Count).End(xlUp).Row))
    Range("CP2").Select
    ActiveCell.FormulaR1C1 = "Yes"
    Range("cp2").Copy Range("cp2", Range("cp2" & Range("d" & Rows.Count).End(xlUp).Row))
    Range("CQ2").Select
    ActiveCell.FormulaR1C1 = "No"
    Range("cq2").Copy Range("cq2", Range("cq2" & Range("d" & Rows.Count).End(xlUp).Row))
    Columns("BH:BH").Select
    Selection.Copy
    Columns("CR:CR").Select
    ActiveSheet.Paste
    Sheets("Formulas").Select
    Range("A6:R6").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Output").Select
    Range("CA1").Select
    ActiveSheet.Paste
    Columns("CA:CR").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Columns("A:BZ").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlToLeft
    Cells.Select
    With Selection.Interior
    .Pattern = xlNone
    .TintAndShade = 0
    .PatternTintAndShade = 0
    End With
    Sheets("Start").Select
    Application.ScreenUpdating = True
    MsgBox "Conversion Complete!"
    End Sub
    Attached Files Attached Files
    Last edited by LarryLegend; 10-18-2013 at 10:18 AM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Need Help with finding the Max Value for every few rows
    By Gt12Ry7Wl in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-02-2013, 09:50 AM
  2. Finding max value in consecutive rows
    By paulchemguy in forum Excel General
    Replies: 2
    Last Post: 06-03-2011, 11:22 PM
  3. Matching rows in excel and finding out duplicate rows
    By ASP__DEVELOPER in forum Excel General
    Replies: 23
    Last Post: 08-17-2010, 06:20 PM
  4. Replies: 3
    Last Post: 03-02-2007, 07:13 AM
  5. Finding a value within two rows
    By Joe Gieder in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 11-03-2005, 12:55 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1