Ask Me Help Desk

Ask Me Help Desk (https://www.askmehelpdesk.com/forum.php)
-   Spreadsheets (https://www.askmehelpdesk.com/forumdisplay.php?f=395)
-   -   Selecting Last Row of data and Offsetting 1 down (https://www.askmehelpdesk.com/showthread.php?t=522038)

  • Nov 1, 2010, 01:45 PM
    jakester
    Selecting Last Row of data and Offsetting 1 down
    I'm trying to write a macro that finds the last row of data and then selects the first empty row after that. Here's what my code looks like:

    Dim LR As Long, LRO As String

    LR = ActiveSheet.UsedRange.Rows.Count

    LRO = "ActiveSheet!R" & LR & C1 & LR & C1

    Columns("G:G").Select
    Selection.Replace What:="Null", Replacement:="", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False

    Columns("A:A").EntireColumn.AutoFit

    Rows(LRO).Offset(1, 0).Select

    End Sub


    I actually got this to work once but for some reason it's not working anymore. I now get a run-type error 13 Type Mismatch

    Any thoughts.

    Thanks, guys.
  • Nov 1, 2010, 05:03 PM
    JBeaucaire

    PROBLEM #1: UsedRange.Rows.Count

    I know a lot of people do it, but you should never use the UsedRange method to determine last row of data. It's not doing what you think it's doing, even though it works 95% of the time. And when it fails you won't know why, or worse, you won't know it's happened and rows of data are disappearing off your database. Oops!

    Try this. Open a blank workbook. Go to cell A3 and enter anything.

    Now open the VBEditor and CTRL-G to open the immediate window. Now enter this line of code from your macro:
    Code:

    ? ActiveSheet.UsedRange.Rows.Count
    The Immediate Window will respond with a 1 instead of 3. That's because it's counting the number of rows in the USEDRANGE, it's not telling you the last row with data. It's not the same thing at all.


    OPTIONAL PROBLEM #2: LastCell

    If you Google for other options, you might stumble on to the xlLastCell method. But this is also a problem because Excel is awful at remembering what the ACTUAL last used cell is on a worksheet, something it should be good at.

    Well, it's not. Don't fall for that method either.

    ==========

    So, lesson #1, here are my two favorite code snippets for determining the last row of data... or in your case the next empty row right below that.

    1) Look UP a specific column, typically column A
    Code:

    NR = Range("A" & Rows.Count).End(xlUp).Row + 1

    2) Quickly check all cells on the activesheet and find the last row with anything anywhere actually in a cell.
    Code:

    NR = Cells.Find("*", Cells(Rows.Count, Columns.Count), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1

    There are good reasons to use each of the two methods above, #1 is my most frequent because I know which columns are critical and always have data in them, so I know which column to base the search on.

    =========

    Now, as to your macro, can you explain in a non-code-way what you're actually trying to do. I can see several issues with the code and fixing it properly (and efficiently) requires I better understand in words the full process you're going through.

    A sample workbook with your desired BEFORE/AFTER examples would be the most clarifying.
  • Nov 2, 2010, 06:10 PM
    jakester
    Quote:

    Originally Posted by JBeaucaire View Post
    PROBLEM #1: UsedRange.Rows.Count

    I know a lot of people do it, but you should never use the UsedRange method to determine last row of data. It's not doing what you think it's doing, even though it works 95% of the time. And when it fails you won't know why, or worse, you won't know it's happened and rows of data are disappearing off your database. Oops!

    Try this. Open a blank workbook. Go to cell A3 and enter anything.

    Now open the VBEditor and CTRL-G to open the immediate window. Now enter this line of code from your macro:
    Code:

    ? ActiveSheet.UsedRange.Rows.Count
    The Immediate Window will respond with a 1 instead of 3. That's because it's counting the number of rows in the USEDRANGE, it's not telling you the last row with data. It's not the same thing at all.


    OPTIONAL PROBLEM #2: LastCell

    If you Google for other options, you might stumble on to the xlLastCell method. But this is also a problem because Excel is awful at remembering what the ACTUAL last used cell is on a worksheet, something it should be good at.

    Well, it's not. Don't fall for that method either.

    ==========

    So, lesson #1, here are my two favorite code snippets for determining the last row of data...or in your case the next empty row right below that.

    1) Look UP a specific column, typically column A
    Code:

    NR = Range("A" & Rows.Count).End(xlUp).Row + 1

    2) Quickly check all cells on the activesheet and find the last row with anything anywhere actually in a cell.
    Code:

    NR = Cells.Find("*", Cells(Rows.Count, Columns.Count), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1

    There are good reasons to use each of the two methods above, #1 is my most frequent because I know which columns are critical and always have data in them, so I know which column to base the search on.

    =========

    Now, as to your macro, can you explain in a non-code-way what you're actually trying to do. I can see several issues with the code and fixing it properly (and efficiently) requires I better understand in words the full process you're going through.

    A sample workbook with your desired BEFORE/AFTER examples would be the most clarifying.

    Man, you have helped me out so many times with my questions that I decided to send a donation your way of $25.00. You deserve it, Jerry... you do great work and are very thorough in your explanation of VBA issues.

    Thanks, man. Enjoy a dinner and beer on me!
  • Nov 2, 2010, 09:03 PM
    JBeaucaire

    Well thank you very much! I'll treat the wife to some pasta, and let her know it's from you!

    What about your actual macro? Are we going to do a BEFORE / AFTER to do some work on that, or did the new lastrow trick accidentally resolve the issue?
  • Nov 4, 2010, 11:50 AM
    jakester
    the macro involving the code you gave me worked fine. I am having trouble with the below code, though. I'm trying to create a worksheet for each day of a given month and name that worksheet accordingly. So, my input box will ask for the month and year I want and creates a copy of an existing worksheet (which I want for formatting purposes) "n" number of times per the days in the month. And then each worksheet will be named: 11.01.10, 11.02.10, etc.

    However, my code is not working properly because the days are not matching the months. Can you take a look and see what I may be doing incorrectly?

    Thanks, man.

    Sub SheetCopy()
    '
    '
    '
    Dim strDate As String
    Dim NumDays As Integer
    Dim I As Long

    Dim sh As Object

    On Error Go to endit

    Application.ScreenUpdating = False


    ' The Do statement captures the Month/Year via the Input Box and then returns number of days in the
    ' month to the NumDays variable

    Do
    strDate = Application.InputBox( _
    Prompt:="Please enter month and year: mm/yyyy", _
    Title:="Month and Year", _
    Default:=Format(Date, "mm/yyyy"), _
    Type:=2)

    If strDate = "False" Then Exit Sub

    If IsDate(strDate) Then
    Exit Do
    Else
    MsgBox "Please enter a valid date, such as ""01/2010"".", vbExclamation, "Invalid Date"
    End If

    Loop

    NumDays = Day(DateSerial(Year(strDate), Month(strDate) + 1, -1))


    ' For each day, the For statement below copies the ActiveSheet 'n' number of times

    For I = 1 To NumDays

    ActiveSheet.copy after:=ActiveSheet

    Next I

    Application.ScreenUpdating = True

    endit:


    ' The For statement below renames each worksheet according to date values for given month

    For Each sh In ActiveWorkbook.Sheets

    I = I + 1

    sh.Name = Format(DateSerial(Year(strDate), Month(strDate), I), "mm.dd.yy")

    Next sh


    End Sub
  • Nov 4, 2010, 03:22 PM
    JBeaucaire

    https://www.askmehelpdesk.com/spread...ml#post2591022

  • All times are GMT -7. The time now is 04:39 AM.