View Full Version : Solved: Select the last used row in a column using VBA
felipesaloma
05-20-2012, 02:39 PM
Hi guys, i would like to select using vba last used row in a Columm
I used this code:
Sub LastRowInOneColumn()
'Find the last used row in a Column: column A in this example
Dim LastRow As Long
With ActiveSheet
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With
MsgBox LastRow
End Sub
Its return MsgBox: 26 , i would like to modify this code to instead MsgBox 26, select the row 26 (or Cell C26).
Thanks,
Felipe
Paul_Hossler
05-20-2012, 03:12 PM
Try
Activesheet.Cells(.Rows.Count, 1).End(xlUp).Select
or
Activesheet.Cells(.Rows.Count, 1).End(xlUp).EntireRow.Select
to select the last cell with data in column A on the activesheet, or the entire row
Paul
felipesaloma
05-20-2012, 03:42 PM
Try
Activesheet.Cells(.Rows.Count, 1).End(xlUp).Select
or
Activesheet.Cells(.Rows.Count, 1).End(xlUp).EntireRow.Select
to select the last cell with data in column A on the activesheet, or the entire row
Paul
Worked Perfectly. Thank you very much !!
felipesaloma
05-20-2012, 04:30 PM
Worked Perfectly. Thank you very much !!
I Had idea with your solution. See if you can help me:
Now, automatically i have used entire last row selected, for example, last Row Number is 26, i would like to select entire row for 19 to 26 (last row) and delete these rows, do you know what code should i use ?
Sory for bad english, i am brazilliam.
Thanks for all
Paul_Hossler
05-20-2012, 06:05 PM
Try this
Option Explicit
Sub test()
'End() ... last row in col A with data = cell A26
'Offset ....row 26 - 7 = 19 = cell A19
'Resize .... A19 for 7 rows = A19:A26
'EntireRow .... = A19:XFD6
ActiveSheet.Cells(ActiveSheet.Rows.Count, 1).End(xlUp).Offset(-7, 0).Resize(7, 1).EntireRow.Delete
End Sub
Paul
felipesaloma
05-20-2012, 06:37 PM
Try this
Option Explicit
Sub test()
'End() ... last row in col A with data = cell A26
'Offset ....row 26 - 7 = 19 = cell A19
'Resize .... A19 for 7 rows = A19:A26
'EntireRow .... = A19:XFD6
ActiveSheet.Cells(ActiveSheet.Rows.Count, 1).End(xlUp).Offset(-7, 0).Resize(7, 1).EntireRow.Delete
End Sub
Paul
It worked, but problem that last row is aways changing, can be Row 26, 29, 38, 68... I will never know
With your code, aways delete last 7 rows
i would like to use my code with your code:
With ActiveSheet
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
ActiveSheet.Cells(.Rows.Count, 1).End(xlUp).EntireRow.Select
MsgBox = LastRow
End With
My code give last row number, i would like to delete this last row to row 19 (this wont never change)..
Understood ?
Thanks for your help
Hello Felipe,
Try:
Option Explicit
Sub example()
Dim lLastRow As Long
With ActiveSheet
lLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
If lLastRow >= 19 Then
.Rows("19:" & lLastRow).Delete
End If
End With
End Sub
felipesaloma
05-20-2012, 07:34 PM
It Worked great with this code:
Sub RESETAR()
'Find the last used row in a Column: column A in this example
Dim LastRow As Long
With ActiveSheet
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
ActiveSheet.Cells(.Rows.Count, 1).End(xlUp).EntireRow.Select
LastRow = LastRow - 17
If LastRow = 0 Then
Else
ActiveSheet.Cells(ActiveSheet.Rows.Count, 1).End(xlUp).Offset(-LastRow, 0).Resize(LastRow, 1).EntireRow.Delete
End If
End With
End Sub
Now problem that i have is that on sheet "Cadastro" i have code to automatically create new line (using macro) if have any change in C26:C50000
When code delete rows, it change value and i get error, i would like to disable this code to create new line and enable again after delete rows.
See Code that i use to create new line automatically:
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Not Intersect(Target, Range("C26:C50000")) Is Nothing Then
Call Módulo2.Macro3
End If
Application.EnableEvents = True
End Sub
Sub Macro3()
'
' Macro3 Macro
'
'
Application.ScreenUpdating = False
ActiveCell.Rows("1:1").EntireRow.Select
Selection.Copy
ActiveCell.Offset(1, 0).Rows("1:1").EntireRow.Select
Selection.Insert Shift:=xlDown
Sheets("Impostos").Select
Call Módulo5.LastRowInOneColumn
ActiveCell.Rows("1:1").EntireRow.Select
Application.CutCopyMode = False
Selection.Copy
ActiveCell.Offset(1, 0).Rows("1:1").EntireRow.Select
Selection.Insert Shift:=xlDown
Sheets("Estimativas Finais").Select
Call Módulo5.LastRowInOneColumn
ActiveCell.Rows("1:1").EntireRow.Select
Application.CutCopyMode = False
Selection.Copy
ActiveCell.Offset(1, 0).Rows("1:1").EntireRow.Select
Selection.Insert Shift:=xlDown
Sheets("Order List").Select
Call Módulo5.LastRowInOneColumn
ActiveCell.Rows("1:1").EntireRow.Select
Selection.Copy
ActiveCell.Offset(1, 0).Rows("1:1").EntireRow.Select
Selection.Insert Shift:=xlDown
Sheets("Cadastro").Select
ActiveCell.Offset(0, 2).Range("A1:C1").Select
Selection.ClearContents
ActiveCell.Offset(-1, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "1"
ActiveCell.Offset(1, -3).Range("A1:C1").Select
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = ""
ActiveCell.Offset(0, -3).Range("A1:C1").Select
End Sub
Did you understood, i think that now i am near a complete solution, i only have to solve this little issue with new line created automatically after any kind of changes..
Thanks for all
felipesaloma
05-20-2012, 08:16 PM
I already solved problems, i used
Application.EnableEvents = False
Code here
Application.EnableEvents = True
Now all is working fine, thank you Paul_Hossler and GTO.
Bob Phillips
05-21-2012, 12:04 AM
When you use With, you do not need to repeat the object within that clause,
Sub RESETAR()
'Find the last used row in a Column: column A in this example
Dim LastRow As Long
With ActiveSheet
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
.Cells(.Rows.Count, 1).End(xlUp).EntireRow.Select
LastRow = LastRow - 17
If LastRow <> 0 Then
.Cells(.Rows.Count, 1).End(xlUp).Offset(-LastRow, 0).Resize(LastRow, 1).EntireRow.Delete
End If
End With
End Sub
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.