|
|
|
|
Senior Member
|
|
Nov 1, 2010, 01:45 PM
|
|
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.
|
|
|
Software Expert
|
|
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:
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.
|
|
|
Senior Member
|
|
Nov 2, 2010, 06:10 PM
|
|
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!
|
|
|
Software Expert
|
|
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?
|
|
|
Senior Member
|
|
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
|
|
|
Software Expert
|
|
Nov 4, 2010, 03:22 PM
|
|
|
|
Question Tools |
Search this Question |
|
|
Check out some similar questions!
Collect data into 2 combo boxes from data in an access table
[ 1 Answers ]
I have two combo boxes. When the form loads the db connection opens a recordset that selects all the data from the retaillocations table. When you choose the region (0-4), that number loads into a text box and the Contacts load into the forst combo box. Now I want to select only the Name based on...
Reverse row data
[ 1 Answers ]
How can I reverse the data in row for example : I enter the data
(1,2,3,4,5) and I want it in (5,4,3,2,1) in the same row :rolleyes:
Offsetting a shower drain trap?
[ 5 Answers ]
A friend is having a shower installed in a second floor bathroom, directly over a stairway landing. The floor joists are only 4" deep, such that a trap under the shower would protrude into the head space in the stairwell. The plumber gave two options: raise the shower floor four inches, such...
View more questions
Search
|