Find Last column Issue

Shadkng

Active Member
Joined
Oct 11, 2018
Messages
250
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.

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
 

Some videos you may like

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
3,974
.
This line :

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

Is finding the Last Column used in Row 31.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,728
Office Version
  1. 365
Platform
  1. Windows
If you are going past col Z, use
VBA Code:
Range("A" & fr ,cells(lr,LastCol)).Select
 

offthelip

Well-known Member
Joined
Dec 23, 2017
Messages
1,743
Office Version
  1. 2010
Platform
  1. Windows
The reason it errors out is because the programmer was converting a column number into a column letter using the the code:
VBA 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 :
Code:
Range("A" & fr ,cells(lr,lastcol)).Select
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,728
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

This line also looks a bit suspect
VBA Code:
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

Active Member
Joined
Oct 11, 2018
Messages
250
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.

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

[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
 
Last edited by a moderator:

offthelip

Well-known Member
Joined
Dec 23, 2017
Messages
1,743
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

See my comment #4
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,728
Office Version
  1. 365
Platform
  1. Windows
This
VBA Code:
LastCol = ActiveSheet.UsedRange.Columns.Count
is perfectly ok, but this
VBA Code:
Range("A" & fr & ":" & lc & lr).Select
needs to be changed to
VBA Code:
Range("A" & fr ,cells(lr,LastCol)).Select
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,728
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,987
Messages
5,627,991
Members
416,285
Latest member
KBray960

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top