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
⬛◼◾▪ CONTATO ▪◾◼⬛
