✔ VBA Excel Specialist®: xlPrevious

Mostrando postagens com marcador xlPrevious. Mostrar todas as postagens

Qual é a Última Coluna da Planilha?

Em vários momentos tentamos determinar qual é a última coluna de uma planilha na qual estamos implementando alguma automação. Ter uma função pronta para uso é sempre uma mão na roda.

Function FindLastColumn (nOpt As Boolean) As Variant
' © 2007-20 A&A - In Any Place®, except where noted, all rights reserved.
' THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT
' LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE.
' Feel free to use the code as you wish but kindly keep this header section intact.
' Copyright© A&A - In Any Place®, all Rights Reserved. ' Author: André Bernardes
' Contact: andreluizbernardess@gmail.com | https://goo.gl/EUMbSe/
' Description: Determina a últma coluna.

Dim LastColumn As Integer
Dim ColumnLetter As String

' Retorna o número da coluna.
If nOpt Then
If WorksheetFunction.CountA(Cells) > 0 Then
' Procura qualquer entrada, pesquisando antes das Colunas.
Let LastColumn = Cells.Find(What:="*", After:=[A1], _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious).Column

Let FindLastColumn = LastColumn
End If
Else ' Retorna a letra da coluna.
If WorksheetFunction.CountA(Cells) > 0 Then
' Procura qualquer entrada, pesquisando antes das Colunas.
Let LastColumn = Cells.Find(What:="*", After:=[A1], _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious).Column

If LastColumn > 26 Then
Let ColumnLetter = Chr(Int((LastColumn - 1) / 26) + 64) & _
Chr(((LastColumn - 1) Mod 26) + 65)
Else
Let ColumnLetter = Chr(LastColumn + 64)
End If

Let FindLastColumn = ColumnLetter
End If
End If
End Function

⬛◼◾▪ Social Media ▪◾◼⬛

• FACEBOOK • TWITTER • INSTAGRAM • TUMBLR • GOOGLE+ • LINKEDIN • PINTEREST

⬛◼◾▪ Blogs ▪◾◼⬛

• VBA Specialist ® • VBA Excel Specialist®

• VBA Access Specialist® • VBA Dashboards Specialist®

• VBA Infografics Specialist® • VBA Pharma Specialist®

⬛◼◾▪ CONTATO ▪◾◼⬛

brazilsalesforceeffectiveness@gmail.com

Qual é a Última Linha da Planilha?

Existem várias situações onde precisamos determinar qual é a última Linha de uma planilha na qual estamos implementando alguma automação. Ter uma função pronta para uso é sempre uma mão na roda. Claro, existem diversas formas de se fazer isso.

Function FindingLastRow (nSheet As String, nColumnAnalyse As String) As Long
' © 2007-20 A&A - In Any Place®, except where noted, all rights reserved.
' THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT
' LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE.
' Feel free to use the code as you wish but kindly keep this header section intact.
' Copyright© A&A - In Any Place®, all Rights Reserved. ' Author: André Bernardes
' Contact: andreluizbernardess@gmail.com | https://goo.gl/EUMbSe/
' Description: Different ways to find the last row number of a range
' SOURCE: www.TheSpreadsheetGuru.com

Dim sht As Worksheet
Dim LastRow As Long

Set sht = ThisWorkbook.Worksheets(nSheet)

' Ctrl + Shift + End
'Let LastRow = sht.Cells(sht.Rows.Count, "A").End(xlUp).Row

' Using UsedRange
'sht.UsedRange 'Refresh UsedRange
'Let LastRow = sht.UsedRange.Rows(sht.UsedRange.Rows.Count).Row

'Using Table Range
'Let LastRow = sht.ListObjects("Table1").Range.Rows.Count

'Using Named Range
'Let LastRow = sht.Range("MyNamedRange").Rows.Count

'Ctrl + Shift + Down (Range should be first cell in data set)
'Let LastRow = sht.Range("D1").CurrentRegion.Rows.Count

' Retorna a última Linha da principal coluna.
Let LastRow = Sheets(nSheet).Range(nColumnAnalyse & Rows.Count).End(xlUp).Row

Let FindingLastRow = LastRow

End Function