PDA

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

GTO
05-20-2012, 06:47 PM
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