Solved VBA- go to end of data

Microsoft Excel 2003 (full product)
February 18, 2010 at 08:54:39
Specs: Windows XP
I get many worksheets, all in the same format, and make the exact same changes to each of them. I want to write a macro to do this for me but the rows of data vary in each worksheet. Instead of selecting rows (12:77), I would like it to select rows 12: last row of data (or blank row) but don't know how to write this. Can someone help?

Here's how it looks now...

Rows("12:77").Select
Selection.Sort Key1:=Range("B12"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
ActiveWindow.SmallScroll Down:=-72
Columns("E:E").Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Range("E12").Select
ActiveCell.FormulaR1C1 = "=LEFT(RC[-3],4)"
Range("E12").Select
ActiveCell.FormulaR1C1 = "=MID(RC[-3],2,4)"
Range("E12").Select
ActiveCell.FormulaR1C1 = "=MID(RC[-3],2,5)"
Range("E12").Select
Selection.Copy
Range("E13:E77").Select
ActiveSheet.Paste
ActiveWindow.SmallScroll Down:=-69
Range("E12:E77").Select
Application.CutCopyMode = False
Selection.Copy
ActiveWindow.SmallScroll Down:=-63
Range("F12").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns("E:E").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Columns("B:B").Select
Selection.ColumnWidth = 39.43
ActiveWindow.SmallScroll Down:=-6
End Sub


See More: VBA- go to end of data

Report •

✔ Best Answer
February 18, 2010 at 14:20:11
Hi,

Here is code that loops through all the worksheets in your workbook, except one sheet - enter it's name in the code here:
'This worksheet is not to be sorted
strNotWs = "Sheet1"

Then for each of the remaining worksheets it finds the last used row in column B

It creates a range of cells from B12 to the last used row in column Z
Change the columns as required - I preferred to sort the columns with data rather than all columns in each row.

After the sort, it adds a new column after Column E
In the new column E and in columns F and G it enters the three string formulas

I am not sure what you were trying to do using the RC notation, so I used standard A1 notation - you should be able to change this to meet your needs more easily.

The string formulas are applied from row 12 to last used row in the three columns.

I had them all using column D as the source.
Although the formulas all have D12, you will find that after the macro runs E13 will refer to D13, E14 will refer to D14 etc.

If you had used $D$12, all the formulas would have referred D12, and would not have changed on successive rows.

As always, test this on a copy of your workbook - the changes cannot be undone with 'Undo'

Because my code requires the last row number in several places, I started by creating the row number as text:
strLastUsedRow = CStr(wsEach.Range("B" & CStr(Application.Rows.Count)).End(xlUp).Row)

and then used strLastUsedRow in several places.

Sub MySort()
Dim wsEach As Worksheet
Dim strNotWs As String
Dim strLastUsedRow As String
Dim rngSort As Range

'This worksheet is not to be sorted
strNotWs = "Sheet1"

For Each wsEach In ActiveWorkbook.Worksheets()
    If wsEach.Name <> strNotWs Then
        'find last used row and setup sort range
        strLastUsedRow = CStr(wsEach.Range("B" & CStr(Application.Rows.Count)) _
                        .End(xlUp).Row)
        Set rngSort = Worksheets(wsEach.Name).Range("B12:Z" & strLastUsedRow)
        'sort all used rows from row 12 down, using column B as the index
        rngSort.Sort Key1:=Worksheets(wsEach.Name).Range("B12:B" & strLastUsedRow), _
                Order1:=xlAscending, _
                Header:=xlNo, _
                MatchCase:=False
        'add a column
        wsEach.Range("E:E").Columns.Insert
        'add formulas
        wsEach.Range("E12:E" & strLastUsedRow).Formula = "=Left(D12,4)"
        wsEach.Range("F12:F" & strLastUsedRow).Formula = "=Mid(D12,2,4)"
        wsEach.Range("G12:G" & strLastUsedRow).Formula = "=Mid(D12,2,5)"
    End If
Next wsEach
End Sub

Hope this gets you going again.

Regards



#1
February 18, 2010 at 10:00:50
Ignoring your code, and just answering your direct question, the "standard" code to determine the last row in a given column is as follows:

Option Explicit
Sub FindLastRowByColumn()
Dim lastRow
 lastRow = Range("A" & Rows.Count).End(xlUp).Row
End Sub

You could then use this to select your Rows:

 Rows("12:" & lastRow).Select

Now that assumes that you know that Column A is the "longest" (or the same length as the "longest") column. If you don't know which column contains the most data, you can loop through the columns and let VBA find the longest one. This example checks the first 10 columns:

Option Explicit
Sub FindLastRowByColumn()
Dim myCol, nxt_lastRow, lastRow
 For myCol = 1 To 10
  nxt_lastRow = Cells(Rows.Count, myCol).End(xlUp).Row
   If nxt_lastRow > lastRow Then lastRow = nxt_lastRow
 Next
Rows("12:" & lastRow).Select
End Sub


That said, your code is obviously a recorded macro and therefore very bloated and inefficient.

If you would be willing to share what it is you are trying to do, maybe we can offer some suggestions that will not only get it done a bit more efficiently, but might also help you learn to write your own code - or at least modify recorded code.

There are still times when I will record some actions to get the basic syntax into the VBA editor, but then I go in and modify it to suit my specific needs.


Report •

#2
February 18, 2010 at 10:42:47
Hi,

To find the last used row there are several ways to do it.

You can look at VBA help for UsedRange and CurrentRegion.

However, i tend to use the End xlUp approach.

This is done by selecting a cell at the bottom of a column and then using End xlup.

Select the column that consistently has the last row of data. This may be column B if column A has labels for rows even when they are empty, so:
Dim rngLastUsedRow as range

Set rngLastUsedRow = Worksheets("Sheet1").Range("B" & CStr(Application.Rows.Count)).End(xlUp)

This finds the last used row, so rngLastUsedRow is now set to say B77

You can then use this information to setup your range for each sheet.

Set rngMyRange = Worksheets("Sheet1").Range("A12:M" & CStr(rngLastUsedRow.Row))

If the last used row was 77 then the above formula returns a range A12 to M77

As a note about VBA - you typically don't need to 'Select' cells before doing an operation on them. When you record macros, Excel records scrolling and cell selections, but these are not needed in the code.

For example a recoded macro might do this to bold the font in Cell B71

Sub LongCode()
    ActiveWindow.SmallScroll Down:=42
    Range("B71").Select
    With Selection.Font
        .Name = "Arial"
        .FontStyle = "Bold"
        .Size = 10
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
    End With
End Sub 

when this code is all that is needed:

Sub ShortCode()
ActiveSheet.Range("B71").Font.Bold =True
End Sub

Regards


Report •

#3
February 18, 2010 at 11:40:18
Yes, I just recorded a macro, any help to trim down the code would be most appreciated. I don't know how to put the suggestions into the existing code, I tried them both and get a "Range of object'_Global' Failed message. I have pasted my second version below... Could you tell me how to put them together?
Regarding the 1st relpy, this is what I am trying to do:
I need to sort rows 12 through end of data in column B. Then, need to add a column to the left of E that has this formula but in a real number: "=MID(RC[-3],3,5)" so I can reference via VLookup. The only way I know to do this is to copy and paste down all the rows, then convert to a real number.

Sub BS_Report_edits()
'
' BS_Report_edits Macro
' Macro recorded 2/18/2010 by AHouk
Dim rngLastUsedRow As Range

Set rngLastUsedRow = Worksheets("Sheet1").Range("B" & CStr(Application.Rows.Count)).End(xlUp)
Set rngMyRange = Worksheets("Sheet1").Range("A12:M" & CStr(rngLastUsedRow.Row))

Range("rngMyRange").Activate
Selection.Sort Key1:=Range("B12"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
ActiveWindow.SmallScroll Down:=-12
Columns("E:E").Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Range("E12").Select
ActiveCell.FormulaR1C1 = "=(MID(RC[-3],3,5))"
Range("E12").Select
ActiveCell.FormulaR1C1 = "=MID(RC[-3],3,5)"
Range("E12").Select
Selection.Copy
Range("E13:rngLastUsedRow").Select
ActiveSheet.Paste
Range("E12:rngLastUsedRow").Select
Range("E47").Activate
Application.CutCopyMode = False
Selection.Copy
Range("F12").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns("E:E").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Selection.ColumnWidth = 6
End Sub


Report •

Related Solutions

#4
February 18, 2010 at 14:00:58
I avoid using UsedRange because it can give misleading results.

Since the UsedRange looks at formatting, comments and data, cells with no data will still be considered as being in the UsedRange, even if they are below the last piece of data in a column.

xlLastCell has it's own set of "issues" so I avoid that also.

I, too, like to stick with the tried and true .End(xlUp)


Report •

#5
February 18, 2010 at 14:20:11
✔ Best Answer
Hi,

Here is code that loops through all the worksheets in your workbook, except one sheet - enter it's name in the code here:
'This worksheet is not to be sorted
strNotWs = "Sheet1"

Then for each of the remaining worksheets it finds the last used row in column B

It creates a range of cells from B12 to the last used row in column Z
Change the columns as required - I preferred to sort the columns with data rather than all columns in each row.

After the sort, it adds a new column after Column E
In the new column E and in columns F and G it enters the three string formulas

I am not sure what you were trying to do using the RC notation, so I used standard A1 notation - you should be able to change this to meet your needs more easily.

The string formulas are applied from row 12 to last used row in the three columns.

I had them all using column D as the source.
Although the formulas all have D12, you will find that after the macro runs E13 will refer to D13, E14 will refer to D14 etc.

If you had used $D$12, all the formulas would have referred D12, and would not have changed on successive rows.

As always, test this on a copy of your workbook - the changes cannot be undone with 'Undo'

Because my code requires the last row number in several places, I started by creating the row number as text:
strLastUsedRow = CStr(wsEach.Range("B" & CStr(Application.Rows.Count)).End(xlUp).Row)

and then used strLastUsedRow in several places.

Sub MySort()
Dim wsEach As Worksheet
Dim strNotWs As String
Dim strLastUsedRow As String
Dim rngSort As Range

'This worksheet is not to be sorted
strNotWs = "Sheet1"

For Each wsEach In ActiveWorkbook.Worksheets()
    If wsEach.Name <> strNotWs Then
        'find last used row and setup sort range
        strLastUsedRow = CStr(wsEach.Range("B" & CStr(Application.Rows.Count)) _
                        .End(xlUp).Row)
        Set rngSort = Worksheets(wsEach.Name).Range("B12:Z" & strLastUsedRow)
        'sort all used rows from row 12 down, using column B as the index
        rngSort.Sort Key1:=Worksheets(wsEach.Name).Range("B12:B" & strLastUsedRow), _
                Order1:=xlAscending, _
                Header:=xlNo, _
                MatchCase:=False
        'add a column
        wsEach.Range("E:E").Columns.Insert
        'add formulas
        wsEach.Range("E12:E" & strLastUsedRow).Formula = "=Left(D12,4)"
        wsEach.Range("F12:F" & strLastUsedRow).Formula = "=Mid(D12,2,4)"
        wsEach.Range("G12:G" & strLastUsedRow).Formula = "=Mid(D12,2,5)"
    End If
Next wsEach
End Sub

Hope this gets you going again.

Regards


Report •

#6
February 18, 2010 at 14:53:16
Works like a charm!! I modified it to be exactly what I need and pasted it below, but it's great! Was wondering if you could tell me how to make column E real numbers so I can reference through VLookup?

I was originally adding a new column E, cutting and special pasting into a new column then converting to real number. Example: cell E13shows 1101 but is really =mid(d13,2,5). I need it to say 1101... Is this possible? You've been so helpful and I hate to ask for more, but figured I'd try. :)

Sub MySort()
Dim wsEach As Worksheet
Dim strNotWs As String
Dim strLastUsedRow As String
Dim rngSort As Range

'This worksheet is not to be sorted
strNotWs = "Sheet1"

For Each wsEach In ActiveWorkbook.Worksheets()
If wsEach.Name <> strNotWs Then
'find last used row and setup sort range
strLastUsedRow = CStr(wsEach.Range("B" & CStr(Application.Rows.Count)) _
.End(xlUp).Row)
Set rngSort = Worksheets(wsEach.Name).Range("B12:Z" & strLastUsedRow)
'sort all used rows from row 12 down, using column B as the index
rngSort.Sort Key1:=Worksheets(wsEach.Name).Range("B12:B" & strLastUsedRow), _
Order1:=xlAscending, _
Header:=xlNo, _
MatchCase:=False
'add a column
wsEach.Range("E:E").Columns.Insert
'add formulas
wsEach.Range("E12:E" & strLastUsedRow).Formula = "=Mid(b12,2,5)"
End If
Next wsEach
End Sub


Report •

#7
February 18, 2010 at 15:30:09
Hi,

One way to do it is to create the number in the macro and save the number in the cell:
wsEach.Range("F12:F" & strLastUsedRow).Value = Mid(wsEach.Range("B12"), 2, 5)

However if the text that the number came from (in column B in this example), changed, the number would not change until the macro was re-run.

Another option is to change the formula you paste to this:
wsEach.Range("E12:E" & strLastUsedRow).Formula = "=Value(Mid(B12,2,5))"

The formula in the cell will convert the text to it's value (number) and if the text in column B changes, so will the number, so I think that this second way is more appropriate

Regards


Report •

#8
February 18, 2010 at 15:49:21
Perfect! I opted for the first version as the data doesn't change once it is exported to Excel, works great!! thanks for all your help, you make my life 10 times easier

Report •

Ask Question