Find Last column Issue

[Shadkng]

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.

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

 

[Logit]

.
This line :

LastCol = ActiveSheet.Cells(31, Columns.Count).End(xlToLeft).Column

Is finding the Last Column used in Row 31.

 

[Fluff]

If you are going past col Z, use

Range("A" & fr ,cells(lr,LastCol)).Select

 

[offthelip]

The reason it errors out is because the programmer was converting a column number into a column letter using the the code:

LastCol = ActiveSheet.Cells(31, Columns.Count).End(xlToLeft).Column
lc = Chr(64 + LastCol)

Since there are only 26 letters in the alphabet this errors only when lastcol is 27
use :

Range("A" & fr ,cells(lr,lastcol)).Select

 

[Fluff]

This line also looks a bit suspect

LastCol = ActiveSheet.Cells(31, Columns.Count).End(xlToLeft).Column

You are looking for the last column on row 31, but the start row seems to vary depending on which sheet your on.

 

[Shadkng]

Sorry Guys, I pasted code that had a line that I was experimenting with. The bolded line is the correct one as the programmer wrote it.

Fluff, that line you mention was suspect was not the correct one.

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

[B]LastCol = ActiveSheet.UsedRange.Columns.Count[/B]
lc = Chr(64 + LastCol)
'MsgBox ("Last Column is: " & lc)

Range("A" & fr & ":" & lc & lr).Select '11/20/2019

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

 

[offthelip]

See my comment #4

 

[Fluff]

This

LastCol = ActiveSheet.UsedRange.Columns.Count

is perfectly ok, but this

Range("A" & fr & ":" & lc & lr).Select

needs to be changed to

Range("A" & fr ,cells(lr,LastCol)).Select

 

[Shadkng]

Fluff this works much better. Thanks everyone for your help!

 

[Fluff]

Glad we could help & thanks for the feedback.