View Full Version : Selecting Last Row of data and Offsetting 1 down
jakester
Nov 1, 2010, 01:45 PM
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.
JBeaucaire
Nov 1, 2010, 05:03 PM
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:
? 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
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.
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.
jakester
Nov 2, 2010, 06:10 PM
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:
? 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
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.
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!
JBeaucaire
Nov 2, 2010, 09:03 PM
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?
jakester
Nov 4, 2010, 11:50 AM
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
JBeaucaire
Nov 4, 2010, 03:22 PM
https://www.askmehelpdesk.com/spreadsheets/vba-code-creating-multiple-worksheets-520153.html#post2591022