Hi,
The below code is used to find the last column for various sheets (activesheet) in my workbook. It has been working perfectly for 3 of the sheets. I just added a 4th sheet called "FAS-CASS" using the same format as the others. However, this sheet returns a column number of 27 which is correct. When I run the full macro I get an error "Method Range of Object_Global failed", and the debug goes to the "bolded" range line below. The last column for the other 3 sheets is 26 or less. The new sheet has 27 columns and it errors out. Is there something in this code that is limiting the columns to 26? This code was written by an excel programmer who recently past away, so I am trying to just modify what he has written. If any additional info is needed please let me know. Any help is appreciated.
The below code is used to find the last column for various sheets (activesheet) in my workbook. It has been working perfectly for 3 of the sheets. I just added a 4th sheet called "FAS-CASS" using the same format as the others. However, this sheet returns a column number of 27 which is correct. When I run the full macro I get an error "Method Range of Object_Global failed", and the debug goes to the "bolded" range line below. The last column for the other 3 sheets is 26 or less. The new sheet has 27 columns and it errors out. Is there something in this code that is limiting the columns to 26? This code was written by an excel programmer who recently past away, so I am trying to just modify what he has written. If any additional info is needed please let me know. Any help is appreciated.
VBA Code:
Sub SORT_FORMS2()
Dim lr As Long
Dim LastCol As Long
Dim lc As String
Dim sht As String '11/20/2019
Dim sc1 As String '11/20/2019
Dim sc2 As String '11/22/2019
ActiveSheet.Select
sht = ActiveSheet.Name '11/20/2019
Select Case sht
Case Is = "TABLE PROD": fr = 37: sc1 = "A": sc2 = "C" 'TABLE PROD
Case Is = "CLUTCH": fr = 31: sc1 = "E": sc2 = "C" 'CLUTCH
Case Is = "CUTTER": fr = 24: sc1 = "U": sc2 = "A" 'CUTTER
Case Is = "FAS-CASS": fr = 31: sc1 = "X": sc2 = "B": 'FAS-CASS
Case Else: Exit Sub
End Select
lr = Range("A" & Rows.Count).End(xlUp).Row
If lr < fr Then Exit Sub
If Len(Range("A" & lr)) < 1 Then 'Required because last cell has a FORMULA with NO data
'ActiveSheet.Range("A" & lr).Delete Shift:=xlUp 'dont delete cell with FORMULA and NO data
lr = lr - 1 - Range("b2")
End If
LastCol = ActiveSheet.Cells(31, Columns.Count).End(xlToLeft).Column
lc = Chr(64 + LastCol)
'MsgBox ("Last Column is: " & lc)
[B]Range("A" & fr & ":" & lc & lr).Select '11/20/2019[/B]
Selection.Sort Key1:=Range(sc1 & fr), Order1:=xlAscending, Key2:=Range(sc2 & fr) _
, Order2:=xlAscending, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, DataOption2:=xlSortNormal
Range("A1").Select
End Sub