Ask Me Help Desk - 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.

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.

Quote:

Originally Posted by 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.

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!

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?

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