Determining The Last Column In Current VBA

S

ShumsFaruk

Board Regular
Joined
Jul 24, 2009
Messages
93
Good Day All,

By subject it sounds familiar, but everyone requires VBA as per their own needs. I found many threads to find the last column but I would like to replace "H" Column to LastCol in below code:

Code: 
Sub Format_UtilizationControl()

Dim Ws1 As Worksheet
Dim LR1 As Long

Set Ws1 = Worksheets("UtilizationControl")
LR1 = Ws1.Range("A" & Rows.Count).End(xlUp).Row
Ws1.Columns("A:H").Copy
Ws1.Range("A1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
Application.CutCopyMode = False
With Ws1.Columns("C:H")
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlCenter
    .WrapText = False
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = False
End With
With Ws1.Columns("A:B")
    .HorizontalAlignment = xlGeneral
    .VerticalAlignment = xlCenter
    .WrapText = False
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = False
End With
Ws1.Range("C1:G1").Merge
With Ws1.Range("C1:G1")
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = True
End With
Ws1.Range("A1").ClearContents
Ws1.Range("A1:A2").Merge
With Ws1.Range("A1:A2")
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = True
End With
Ws1.Range("B1:B2").Merge
With Ws1.Range("B1:B2")
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = True
End With
Ws1.Range("H2").FormulaR1C1 = "Total"
Ws1.Range("H1:H2").Merge
With Ws1.Range("H1:H2")
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = True
End With
Ws1.Range("A1:H2").Font.Bold = True
Ws1.Range("A1:H2").Borders(xlDiagonalDown).LineStyle = xlNone
Ws1.Range("A1:H2").Borders(xlDiagonalUp).LineStyle = xlNone
With Ws1.Range("A1:H2").Borders(xlEdgeLeft)
    .LineStyle = xlContinuous
    .ColorIndex = 1
    .TintAndShade = 0
    .Weight = xlThin
End With
With Ws1.Range("A1:H2").Borders(xlEdgeTop)
    .LineStyle = xlContinuous
    .ColorIndex = 1
    .TintAndShade = 0
    .Weight = xlThin
End With
With Ws1.Range("A1:H2").Borders(xlEdgeBottom)
    .LineStyle = xlContinuous
    .ColorIndex = 1
    .TintAndShade = 0
    .Weight = xlThin
End With
With Ws1.Range("A1:H2").Borders(xlEdgeRight)
    .LineStyle = xlContinuous
    .ColorIndex = 1
    .TintAndShade = 0
    .Weight = xlThin
End With
With Ws1.Range("A1:H2").Borders(xlInsideVertical)
    .LineStyle = xlContinuous
    .ColorIndex = 1
    .TintAndShade = 0
    .Weight = xlThin
End With
With Ws1.Range("A1:H2").Borders(xlInsideHorizontal)
    .LineStyle = xlContinuous
    .ColorIndex = 1
    .TintAndShade = 0
    .Weight = xlThin
End With
With Ws1.Range("A1:H2").Interior
    .Pattern = xlSolid
    .PatternColorIndex = xlAutomatic
    .ThemeColor = xlThemeColorLight2
    .TintAndShade = -0.249977111117893
    .PatternTintAndShade = 0
End With
With Ws1.Range("A1:H2").Font
    .ThemeColor = xlThemeColorAccent6
    .TintAndShade = -0.249977111117893
End With
Ws1.Range("A3").Select
ActiveWindow.FreezePanes = True
Ws1.Range("A3:H" & LR1).Borders(xlDiagonalDown).LineStyle = xlNone
Ws1.Range("A3:H" & LR1).Borders(xlDiagonalUp).LineStyle = xlNone
With Ws1.Range("A3:H" & LR1).Borders(xlEdgeLeft)
    .LineStyle = xlContinuous
    .ColorIndex = 1
    .TintAndShade = 0
    .Weight = xlThin
End With
With Ws1.Range("A3:H" & LR1).Borders(xlEdgeTop)
    .LineStyle = xlContinuous
    .ColorIndex = 1
    .TintAndShade = 0
    .Weight = xlThin
End With
With Ws1.Range("A3:H" & LR1).Borders(xlEdgeBottom)
    .LineStyle = xlContinuous
    .ColorIndex = 1
    .TintAndShade = 0
    .Weight = xlThin
End With
With Ws1.Range("A3:H" & LR1).Borders(xlEdgeRight)
    .LineStyle = xlContinuous
    .ColorIndex = 1
    .TintAndShade = 0
    .Weight = xlThin
End With
With Ws1.Range("A3:H" & LR1).Borders(xlInsideVertical)
    .LineStyle = xlContinuous
    .ColorIndex = 1
    .TintAndShade = 0
    .Weight = xlThin
End With
With Ws1.Range("A3:H" & LR1).Borders(xlInsideHorizontal)
    .LineStyle = xlContinuous
    .ColorIndex = 1
    .TintAndShade = 0
    .Weight = xlThin
End With
Ws1.Range("A" & LR1 & ":B" & LR1).Merge
With Ws1.Range("A" & LR1 & ":B" & LR1)
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlCenter
    .WrapText = False
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = True
End With

Ws1.Range("A" & LR1 & ":H" & LR1).Font.Bold = True
With Ws1.Range("H3:H" & LR1).Interior
    .Pattern = xlSolid
    .PatternColorIndex = xlAutomatic
    .ThemeColor = xlThemeColorLight2
    .TintAndShade = -0.249977111117893
    .PatternTintAndShade = 0
End With
Ws1.Range("H3:H" & LR1).Font.Bold = True
With Ws1.Range("H3:H" & LR1).Font
    .ThemeColor = xlThemeColorAccent6
    .TintAndShade = -0.249977111117893
End With
With Ws1.Range("A" & LR1 & ":H" & LR1).Interior
    .Pattern = xlSolid
    .PatternColorIndex = xlAutomatic
    .ThemeColor = xlThemeColorLight2
    .TintAndShade = -0.249977111117893
    .PatternTintAndShade = 0
End With
With Ws1.Range("A" & LR1 & ":H" & LR1).Font
    .ThemeColor = xlThemeColorAccent6
    .TintAndShade = -0.249977111117893
End With
Ws1.Columns("A:H").EntireColumn.AutoFit
Ws1.Range("A2").Select

End Sub

Above code works fine to format my sheet.

Please help.
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
Sort by date Sort by votes
Upvote 0
....ShumsFaruk.....
:rolleyes:

I can tell you what to do, but i do not feel like doing it all for you, that is to say, I do not want to edit that massive ( possibly macro recording produced ? ) code ...

But basically my alternative to Joe4’s suggestions is to replace H everywhere in your code with a String Variable which contains the letter of the Last Column....( which is fairly easy to determine... as i will explain.. )

_.............................

So, Let me explain you.....

Take a simplified example:

Say I have this:

Using Excel 2007
Row\Col
A​
B​
C​
D​
E​
F​
G​
H​
1​
A1B1C1
2​
A2B2C2
3​
A3B3C3
4​
A4B4C4
5​
A5B5C5
6​
A6B6C6
7​
A7B7C7
8​
A8B8C8
9​
A9B9C9
10​
A10B10C10
ColumnLetter


And say a code initially gives this:


Using Excel 2007
Row\Col
A​
B​
C​
D​
E​
F​
G​
H​
1​
A1B1C1
2​
A2B2C2
3​
A3B3C3
4​
A4B4C4
5​
A5B5C5
6​
A6B6C6
7​
A7B7C7
8​
A8B8C8
9​
A9B9C9
10​
A10B10C10
ColumnLetter

_..

But now say that you want the code modified to give you this:

Using Excel 2007
Row\Col
A​
B​
C​
D​
E​
F​
G​
H​
1​
A1B1C1
2​
A2B2C2
3​
A3B3C3
4​
A4B4C4
5​
A5B5C5
6​
A6B6C6
7​
A7B7C7
8​
A8B8C8
9​
A9B9C9
10​
A10B10C10
ColumnLetter

_...........................................

So how to do that, ...
My take on the thing is to approach like this:

First take a look at and try these codes. These two codes will give the Second Screen Shot Results.

Code: 
[color=darkgreen]'     Post  #  Love Letters    in the VBA    http://www.mrexcel.com/forum/excel-questions/936288-determining-last-column-current-visual-basic-applications.html[/color]
[color=blue]Option[/color] [color=blue]Explicit[/color]
[color=blue]Sub[/color] ShumsFarukLoveColumnLetters()
[color=blue]Dim[/color] ws [color=blue]As[/color] Worksheet: [color=blue]Set[/color] ws = ThisWorkbook.Worksheets("ColumnLoveLetter") [color=darkgreen]'CHANGE TO SUIT Your Sheet !!!![/color]

ws.Range("A1:H10").Interior.Color = 65535
[color=blue]End[/color] [color=blue]Sub[/color]
[color=darkgreen]'[/color]

[color=blue]Sub[/color] ShumsFarukHatchColumnLetters()
[color=blue]Dim[/color] ws [color=blue]As[/color] Worksheet: [color=blue]Set[/color] ws = ThisWorkbook.Worksheets("ColumnLoveLetter") [color=darkgreen]'CHANGE TO SUIT Your Sheet !!!![/color]

[color=blue]Dim[/color] Hatch [color=blue]As[/color] String: [color=blue]Let[/color] Hatch = "H"
ws.Range("A1:" & Hatch & "10").Interior.Color = 65535
[color=blue]End[/color] [color=blue]Sub[/color]
_............

The first code is doing things like what your code is. (_... - If i have not overlooked anything then you are using a H in the bit in the bracket in a Range(___).
This version, Range(___), of assign a Range takes a String.
http://www.mrexcel.com/forum/excel-...-indicate-range-cells-merge.html?#post3917366
This is nice
http://www.mrexcel.com/forum/excel-...vs-[a1-a5]-benefits-dangers.html?#post4330624
as you can then build that string a bit differently. ....)

So I do that string building In the second code, which with...
Dim Hatch As String: Let Hatch = "H"
Creates and “Fills” the String Variable, Hatch, with H.
The usual VBA ampersand convention “glue” stuff
( " & ___ & " )
is then used to join ( stick inside somewhere) a Variable into the original String.
So simply
H
within your original Range(String) is replaced by
" & Hatch & "
_.......................................

Of course the variable Hatch could contain any Letter, such as that of the last Column, which in my example is
C

_...........................

So you could rename that String Variable anything, Like LastColumnLetter, and replace in your original Strings all the
H
With
" & LastColumnLetter & "

_...........................

The rest is then a case of determining early on in your code:

_ 1) As by Joe4, The last Column can be determined as given in Joe4’s first link. Typically those methods give you a column Number. ******
And;
_ 2) ( My alternative approach now ) would be to determine the Column Letter from that number. ( There are some nice Functions kicking about to do that. )

_..........................

So.... Based on all that, This would be the equivalent code to give the Third Screenshot Results.

Code: 
[color=darkgreen]'[/color]
[color=blue]Sub[/color] ShumsFarukLastColumnLetters()
[color=blue]Dim[/color] ws [color=blue]As[/color] Worksheet: [color=blue]Set[/color] ws = ThisWorkbook.Worksheets("ColumnLoveLetter") [color=darkgreen]'CHANGE TO SUIT Your Sheet !!!!  'Set ws = Worksheets("ColumnLoveLetter") ' Using Worksheets Object Collection of All Open Workbooks as alternative[/color]


[color=darkgreen]' Determine Last Column Number( Two typical methods )  '        http://www.mrexcel.com/forum/excel-questions/48638-macro-needs-select-last-column.html#post223306      http://www.mrexcel.com/forum/excel-questions/48638-macro-needs-select-last-column.html[/color]
[color=blue]Dim[/color] lc [color=blue]As[/color] [color=blue]Long[/color] [color=darkgreen]'                                                                                                                         '( Long is a Big whole Number limit (-2,147,483,648 to 2,147,483,647) If you need some sort of validation the value should only be within the range of a Byte/Integer otherwise there's no point using anything but Long.--upon/after 32-bit, Integers (Short) need converted internally anyways, so a Long is actually faster. )[/color]
[color=blue]Let[/color] lc = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column [color=darkgreen]'                ' The Range Object ( cell ) that is the last cell in the row ( 1 here ) of interest, ( Column Number given by .Count Property applied to ( ws here, any would do, so wc can be ommited and the default for the macro is will be used ) Spreadsheet Range Columns Property)    has the Property .End ( argument "Looking to left" ) appled to it. This Returns a new Range ( cell ) object which is that of the first Range ( cell ) with something in it "looking back to the left" in the XL spreadsheet from that last cell. Then the .Column Property is applied to return a long number equal to the Column number of that cell[/color]
[color=blue]Let[/color] lc = ws.Cells.Find(What:="*", After:=ws.Cells(1, 1), SearchDirection:=xlPrevious, SearchOrder:=xlByColumns).Column [color=darkgreen]'The Find Method looks for anything, searching by columns, starting at the first Cell and going backwards, which effectivelly starts at the last column which allows for different XL versions. This will determine the column that has anything in any row rather than looking in a particular row as in the above line.[/color]

[color=darkgreen]' Determine Last Column Letter  ‘There are many ways to get a Column Letter from a Column Number – excelforum.com/tips-and-tutorials/1108643-vba-column-letter-from-column-number-explained.html[/color]
[color=blue]Dim[/color] LastColumnLetter [color=blue]As[/color] [color=blue]String[/color]
[color=blue]Let[/color] LastColumnLetter = FucshgMathsVBA(lc) [color=darkgreen]' Call to Function to determine the last Column Letter from the Last Column Number[/color]

[color=darkgreen]'[/color]
ws.Range("A1:" & LastColumnLetter & "10").Interior.Color = 65535
[color=blue]End[/color] [color=blue]Sub[/color]


[color=darkgreen]'[/color]
'  shg 2016      http://www.excelforum.com/tips-and-tutorials/1108643-vba-column-letter-from-column-number-explained.html#post4221359
[color=blue]Function[/color] FucshgMathsVBA([color=blue]ByVal[/color] lclm [color=blue]As[/color] [color=blue]Long[/color]) [color=blue]As[/color] [color=blue]String[/color] [color=darkgreen]'[/color]
                                            '    Dim vtemp
                                            [color=darkgreen]'    Let vtemp = IIf(((((lclm - 1) \ 26) - 1) \ 26) <> 0, Chr(65 + (((((lclm - 1) \ 26) - 1) \ 26) - 1 Mod 26)), "")[/color]
                                            [color=darkgreen]'    Let vtemp = Evaluate("IF(QUOTIENT(QUOTIENT(" & lclm & "-1, 26)-1, 26), CHAR(MOD(QUOTIENT(QUOTIENT(" & lclm & "-1, 26)-1, 26), 26)-1 + 65), """") ")[/color]
                                            [color=darkgreen]'    Let vtemp = IIf(((lclm - 1) \ 26) <> 0, Chr(65 + (((lclm - 1) \ 26) - 1) Mod 26), "")[/color]
                                            [color=darkgreen]'    Let vtemp = Evaluate("IF(QUOTIENT(" & lclm & "-1, 26), CHAR(MOD(QUOTIENT(" & lclm & "-1, 26)-1, 26) + 65), """")")[/color]
                                            [color=darkgreen]'    Let vtemp = IIf(lclm <> 0, Chr(65 + (lclm - 1) Mod 26), "")[/color]
                                            [color=darkgreen]'    [color=blue]Let[/color] vtemp = Evaluate("IF(" & lclm & ", CHAR(MOD(" & lclm & "-1, 26) + 65), """") & """"")[/color]
                                            [color=darkgreen]'    Let FucshgMathsVBA = IIf(((((lclm - 1) \ 26) - 1) \ 26) <> 0, Chr(65 + (((((lclm - 1) \ 26) - 1) \ 26) - 1 Mod 26)), "") & IIf(((lclm - 1) \ 26) <> 0, Chr(65 + (((lclm - 1) \ 26) - 1) Mod 26), "") & IIf(lclm <> 0, Chr(65 + (lclm - 1) Mod 26), "")[/color]
Let FucshgMathsVBA = IIf(((((lclm - 1) \ 26) - 1) \ 26), Chr(65 + (((((lclm - 1) \ 26) - 1) \ 26) - 1 Mod 26)), "") & IIf(((lclm - 1) \ 26), Chr(65 + (((lclm - 1) \ 26) - 1) Mod 26), "") & IIf(lclm, Chr(65 + (lclm - 1) Mod 26), "")
                                            [color=darkgreen]'    Let FucshgMathsVBA = Evaluate("IF(QUOTIENT(QUOTIENT(" & lclm & "-1, 26)-1, 26), CHAR(MOD(QUOTIENT(QUOTIENT(" & lclm & "-1, 26)-1, 26)-1, 26) + 65), """") & IF(QUOTIENT(" & lclm & "-1, 26), CHAR(MOD(QUOTIENT(" & lclm & "-1, 26)-1, 26) + 65), """") & IF(" & lclm & ", CHAR(MOD(" & lclm & "-1, 26) + 65), """") & """"")[/color]
[color=blue]End[/color] [color=blue]Function[/color]

_.................................................

So to summarise what you have to do:

Early in your code determine lc, ( the last column Number ). ( use either of the two ways I did, depending on what might suit your data )

Use that Number, lc, in a Call to the Letter determining Function ( here I use a shg Function , - which you need to copy to the same module as where your main code is ) to get the Variable LastColumnLetter

Then in your code replace all the H’s in the
Range(_H__)
bits with " & LastColumnLetter & " thus:
Range(_" & LastColumnLetter & "__)

_............................

It is actually very simple, just a bit tedious to do all that editing. You could use the Find Replace, but search one occurrence at a time so you only change the H’s you want to ( those in the Range(___) bits )

Alan

******P.s. Note: There is currently an error in the second of the two replies referenced by Joe4, but in my code I have the correct form...
 
Upvote 0
Thank you very much Joe4 & DocAElstein.

Elstein, I have no words to express gratitude for such brief explanation. Thanks a lot.

It wasn't macro recording produced code :p

When I tried your code:
Code: 
ws.Range("A1:" & Hatch & "10").Interior.Color = 65535
It was giving me error, then I found below code from excel - Select 2nd Row through last Row in Last Column +1 VBA - Stack Overflow
Code: 
Range(Cells(2, lastcolumn+1), Cells(lastrow, lastcolumn+1)).Formula = "=sum(1,1)"
This explains me how to refer from which row to last column. I applied in my initial code and it worked perfectly.

My corrected code is as below:
Code: 
Sub Format_UtilizationControl2()

Dim Ws1 As Worksheet
Dim LR1 As Long
Dim LC1 As Long
Dim LC2 As Long
Dim Rng1 As Range, Rng2 As Range
Set Ws1 = Worksheets("UtilizationControl")
LR1 = Ws1.Range("A" & Rows.Count).End(xlUp).Row
LC1 = Ws1.Cells(2, Ws1.Columns.Count).End(xlToLeft).Column

Ws1.Range(Cells(1, 1), Cells(LR1, LC1)).Copy
Ws1.Range("A1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
Application.CutCopyMode = False
With Ws1.Range(Cells(1, 3), Cells(2, LC1 - 1))
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlCenter
    .WrapText = False
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = False
End With
With Ws1.Columns("A:B")
    .HorizontalAlignment = xlGeneral
    .VerticalAlignment = xlCenter
    .WrapText = False
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = False
End With
Ws1.Range(Cells(1, 3), Cells(1, LC1 - 1)).Merge
With Ws1.Range(Cells(1, 3), Cells(LC1 - 1))
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = True
End With
Ws1.Range("A1").ClearContents
Ws1.Range("A1:A2").Merge
With Ws1.Range("A1:A2")
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = True
End With
Ws1.Range("B1:B2").Merge
With Ws1.Range("B1:B2")
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = True
End With
Ws1.Range(Cells(2, LC1), Cells(2, LC1)).Formula = "Total"
Ws1.Range(Cells(2, LC1), Cells(1, LC1)).Merge
With Ws1.Range(Cells(2, LC1), Cells(1, LC1))
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = True
End With
Ws1.Range(Cells(1, 1), Cells(2, LC1)).Font.Bold = True
Ws1.Range(Cells(1, 1), Cells(2, LC1)).Borders(xlDiagonalDown).LineStyle = xlNone
Ws1.Range(Cells(1, 1), Cells(2, LC1)).Borders(xlDiagonalUp).LineStyle = xlNone
With Ws1.Range(Cells(1, 1), Cells(2, LC1)).Borders(xlEdgeLeft)
    .LineStyle = xlContinuous
    .ColorIndex = 1
    .TintAndShade = 0
    .Weight = xlThin
End With
With Ws1.Range(Cells(1, 1), Cells(2, LC1)).Borders(xlEdgeTop)
    .LineStyle = xlContinuous
    .ColorIndex = 1
    .TintAndShade = 0
    .Weight = xlThin
End With
With Ws1.Range(Cells(1, 1), Cells(2, LC1)).Borders(xlEdgeBottom)
    .LineStyle = xlContinuous
    .ColorIndex = 1
    .TintAndShade = 0
    .Weight = xlThin
End With
With Ws1.Range(Cells(1, 1), Cells(2, LC1)).Borders(xlEdgeRight)
    .LineStyle = xlContinuous
    .ColorIndex = 1
    .TintAndShade = 0
    .Weight = xlThin
End With
With Ws1.Range(Cells(1, 1), Cells(2, LC1)).Borders(xlInsideVertical)
    .LineStyle = xlContinuous
    .ColorIndex = 1
    .TintAndShade = 0
    .Weight = xlThin
End With
With Ws1.Range(Cells(1, 1), Cells(2, LC1)).Borders(xlInsideHorizontal)
    .LineStyle = xlContinuous
    .ColorIndex = 1
    .TintAndShade = 0
    .Weight = xlThin
End With
With Ws1.Range(Cells(1, 1), Cells(2, LC1)).Interior
    .Pattern = xlSolid
    .PatternColorIndex = xlAutomatic
    .ThemeColor = xlThemeColorLight2
    .TintAndShade = -0.249977111117893
    .PatternTintAndShade = 0
End With
With Ws1.Range(Cells(1, 1), Cells(2, LC1)).Font
    .ThemeColor = xlThemeColorAccent6
    .TintAndShade = -0.249977111117893
End With
Ws1.Range("A3").Select
ActiveWindow.FreezePanes = True
Ws1.Range(Cells(3, 1), Cells(LR1, LC1)).Borders(xlDiagonalDown).LineStyle = xlNone
Ws1.Range(Cells(3, 1), Cells(LR1, LC1)).Borders(xlDiagonalUp).LineStyle = xlNone
With Ws1.Range(Cells(3, 1), Cells(LR1, LC1)).Borders(xlEdgeLeft)
    .LineStyle = xlContinuous
    .ColorIndex = 1
    .TintAndShade = 0
    .Weight = xlThin
End With
With Ws1.Range(Cells(3, 1), Cells(LR1, LC1)).Borders(xlEdgeTop)
    .LineStyle = xlContinuous
    .ColorIndex = 1
    .TintAndShade = 0
    .Weight = xlThin
End With
With Ws1.Range(Cells(3, 1), Cells(LR1, LC1)).Borders(xlEdgeBottom)
    .LineStyle = xlContinuous
    .ColorIndex = 1
    .TintAndShade = 0
    .Weight = xlThin
End With
With Ws1.Range(Cells(3, 1), Cells(LR1, LC1)).Borders(xlEdgeRight)
    .LineStyle = xlContinuous
    .ColorIndex = 1
    .TintAndShade = 0
    .Weight = xlThin
End With
With Ws1.Range(Cells(3, 1), Cells(LR1, LC1)).Borders(xlInsideVertical)
    .LineStyle = xlContinuous
    .ColorIndex = 1
    .TintAndShade = 0
    .Weight = xlThin
End With
With Ws1.Range(Cells(3, 1), Cells(LR1, LC1)).Borders(xlInsideHorizontal)
    .LineStyle = xlContinuous
    .ColorIndex = 1
    .TintAndShade = 0
    .Weight = xlThin
End With
Ws1.Range("A" & LR1 & ":B" & LR1).Merge
With Ws1.Range("A" & LR1 & ":B" & LR1)
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlCenter
    .WrapText = False
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = True
End With

Ws1.Range("A" & LR1 & LC1 & LR1).Font.Bold = True
With Ws1.Range(Cells(1, LC1), Cells(LR1, LC1)).Interior
    .Pattern = xlSolid
    .PatternColorIndex = xlAutomatic
    .ThemeColor = xlThemeColorLight2
    .TintAndShade = -0.249977111117893
    .PatternTintAndShade = 0
End With
Ws1.Range(Cells(3, LC1), Cells(LR1, LC1)).Font.Bold = True
With Ws1.Range(Cells(3, LC1), Cells(LR1, LC1)).Font
    .ThemeColor = xlThemeColorAccent6
    .TintAndShade = -0.249977111117893
End With
With Ws1.Range(Cells(LR1, 1), Cells(LR1, LC1)).Interior
    .Pattern = xlSolid
    .PatternColorIndex = xlAutomatic
    .ThemeColor = xlThemeColorLight2
    .TintAndShade = -0.249977111117893
    .PatternTintAndShade = 0
End With
With Ws1.Range(Cells(LR1, 1), Cells(LR1, LC1)).Font
    .ThemeColor = xlThemeColorAccent6
    .TintAndShade = -0.249977111117893
End With
Ws1.Range(Cells(LR1, 1), Cells(LR1, LC1)).Font.Bold = True
With Ws1.Range(Cells(3, 3), Cells(LR1 - 1, LC1 - 1))
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlCenter
    .WrapText = False
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = False
End With
Ws1.Range(Cells(1, 1), Cells(LR1, LC1)).EntireColumn.AutoFit

Ws1.Range("A2").Select

End Sub

Thanks once again. You really did a lot of work for me. Salute to you.
 
Upvote 0
Hi ShumsFaruk

ShumsFaruk said:
Thank you very much Joe4 & DocAElstein.
Thanks ...... You really did a lot of work for me. Salute to you.
Click to expand...

You are welcome, thanks for the feedback.. :)
And thanks for sharing your soolution! :)
_.....................................................


ShumsFaruk said:
Elstein, ...........

When I tried your code:
Code: 
ws.Range("A1:" & Hatch & "10").Interior.Color = 65535
It was giving me error, .............
Click to expand...

What was the error ?

- Remember to change the ws referrecnce to the name of your sheet
.. maybe
"Sheet1"
or
"UtilizationControl"
instead of my
"ColumnLoveLetter"

Or just remove all
ws.
and use, for example:

Code: 
Range("A1:" & Hatch & "10").Interior.Color = 65535

The codes will then work on the current ActiveSheet ( the one you are "looking at" )

Alan
 
Last edited:
Upvote 0
Just to complete my demo codes using ShumsFaruk solution way....
ShumsFaruk said:
........ then I found below code from excel - Select 2nd Row through last Row in Last Column +1 VBA - Stack Overflow
......
Click to expand...

_...................................

Following demo codes, for example working on this:

Using Excel 2007
Row\Col
A​
B​
C​
D​
E​
F​
G​
H​
1​
A1B1C1
2​
A2B2C2
3​
A3B3C3
4​
A4B4C4
5​
A5B5C5
6​
A6B6C6
7​
A7B7C7
8​
9​
10​
ColumnLoveLetter

_...............................................................

Now, These codes:
Code: 
[color=darkgreen]'[/color]
[color=blue]Sub[/color] ShumsFarukSolution1()  '      http://www.mrexcel.com/forum/excel-questions/936288-determining-last-column-current-visual-basic-applications.html#post4499161
[color=blue]Dim[/color] ws [color=blue]As[/color] Worksheet: [color=blue]Set[/color] ws = ThisWorkbook.Worksheets("ColumnLoveLetter") [color=darkgreen]'CHANGE TO SUIT Your Sheet !!!![/color]
ws.Range(ws.Cells(1, 1), ws.Cells(10, 8)).Interior.Color = 65535
[color=blue]End[/color] [color=blue]Sub[/color]
[color=darkgreen]'[/color]
[color=blue]Sub[/color] ShumsFarukSolution1ActiveSheetVersion() 'Code in Normal Code Module to work on Active Sheet
Range(Cells(1, 1), Cells(10, 8)).Interior.Color = 65535
[color=blue]End[/color] [color=blue]Sub[/color]

Give this:

Using Excel 2007
Row\Col
A​
B​
C​
D​
E​
F​
G​
H​
1​
A1B1C1
2​
A2B2C2
3​
A3B3C3
4​
A4B4C4
5​
A5B5C5
6​
A6B6C6
7​
A7B7C7
8​
9​
10​
ColumnLoveLetter


_......................................................

and Now for ::::...”..... to refer from which row to last column……“
.....
These codes:
Rich (BB code): 
'
Sub ShumsFarukSolution2()
Dim ws As Worksheet: Set ws = ThisWorkbook.Worksheets("ColumnLoveLetter") 'CHANGE TO SUIT Your Sheet !!!!
Dim lc As Long: Let lc = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
Dim lr As Long: Let lr = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
ws.Range(ws.Cells(1, 1), ws.Cells(lr, lc)).Interior.Color = 65535
End Sub
'
Sub ShumsFarukSolution2ActiveSheetVersion() 'Code in Normal Code Module to work on Active Sheet
Dim lc As Long: Let lc = Cells(1, Columns.Count).End(xlToLeft).Column
Dim lr As Long: Let lr = Cells(Rows.Count, 1).End(xlUp).Row
Range(Cells(1, 1), Cells(lr, lc)).Interior.Color = 65535
End Sub

Gives:

Using Excel 2007
Row\Col
A​
B​
C​
D​
E​
F​
G​
H​
1​
A1B1C1
2​
A2B2C2
3​
A3B3C3
4​
A4B4C4
5​
A5B5C5
6​
A6B6C6
7​
A7B7C7
8​
9​
10​
ColumnLoveLetter

_....................

<marquee direction="down" width="30" height="40" behavior="alternate" >
<marquee behavior="alternate">
<strike>:)</strike>
</marquee>
</marquee> )

<strike>Alan Elston</strike>
 
Upvote 0
DocAElstein said:
Just to complete my demo codes using ShumsFaruk solution way....


_...................................

Following demo codes, for example working on this:

Using Excel 2007
Row\Col
A​
B​
C​
D​
E​
F​
G​
H​
1​
A1B1C1
2​
A2B2C2
3​
A3B3C3
4​
A4B4C4
5​
A5B5C5
6​
A6B6C6
7​
A7B7C7
8​
9​
10​
ColumnLoveLetter

_...............................................................

Now, These codes:
Code: 
[color=darkgreen]'[/color]
[color=blue]Sub[/color] ShumsFarukSolution1()  '      http://www.mrexcel.com/forum/excel-questions/936288-determining-last-column-current-visual-basic-applications.html#post4499161
[color=blue]Dim[/color] ws [color=blue]As[/color] Worksheet: [color=blue]Set[/color] ws = ThisWorkbook.Worksheets("ColumnLoveLetter") [color=darkgreen]'CHANGE TO SUIT Your Sheet !!!![/color]
ws.Range(ws.Cells(1, 1), ws.Cells(10, 8)).Interior.Color = 65535
[color=blue]End[/color] [color=blue]Sub[/color]
[color=darkgreen]'[/color]
[color=blue]Sub[/color] ShumsFarukSolution1ActiveSheetVersion() 'Code in Normal Code Module to work on Active Sheet
Range(Cells(1, 1), Cells(10, 8)).Interior.Color = 65535
[color=blue]End[/color] [color=blue]Sub[/color]

Give this:

Using Excel 2007
Row\Col
A​
B​
C​
D​
E​
F​
G​
H​
1​
A1B1C1
2​
A2B2C2
3​
A3B3C3
4​
A4B4C4
5​
A5B5C5
6​
A6B6C6
7​
A7B7C7
8​
9​
10​
ColumnLoveLetter


_......................................................

and Now for ::::...”..... to refer from which row to last column……“
.....
These codes:
Rich (BB code): 
'
Sub ShumsFarukSolution2()
Dim ws As Worksheet: Set ws = ThisWorkbook.Worksheets("ColumnLoveLetter") 'CHANGE TO SUIT Your Sheet !!!!
Dim lc As Long: Let lc = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
Dim lr As Long: Let lr = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
ws.Range(ws.Cells(1, 1), ws.Cells(lr, lc)).Interior.Color = 65535
End Sub
'
Sub ShumsFarukSolution2ActiveSheetVersion() 'Code in Normal Code Module to work on Active Sheet
Dim lc As Long: Let lc = Cells(1, Columns.Count).End(xlToLeft).Column
Dim lr As Long: Let lr = Cells(Rows.Count, 1).End(xlUp).Row
Range(Cells(1, 1), Cells(lr, lc)).Interior.Color = 65535
End Sub

Gives:

Using Excel 2007
Row\Col
A​
B​
C​
D​
E​
F​
G​
H​
1​
A1B1C1
2​
A2B2C2
3​
A3B3C3
4​
A4B4C4
5​
A5B5C5
6​
A6B6C6
7​
A7B7C7
8​
9​
10​
ColumnLoveLetter

_....................

<marquee direction="down" width="30" height="40" behavior="alternate" >
<marquee behavior="alternate">
<strike>:)</strike>
</marquee>
</marquee> )

<strike>Alan Elston</strike>
Click to expand...

Excellent (y)
 
Upvote 0
Methods to Modify Code to use Last Row and Last Column rather than Hard Coding

Hi ShumsFaruk
ShumsFaruk said:
Excellent (y)
Click to expand...

Thank you!
Alan
:)
_.........



P.s. Just quote a little . – That helps to keep the Thread a bit more readable :)

Like this:

DocAElstein said:
Just to complete my demo codes using ShumsFaruk solution way...
_...................................

Following demo codes, for example working on this:
......
_......................................................
and Now for ::::...”..... to refer from which row to last column……“
.....
These codes:
......Gives:
..........
_....................

<marquee direction="down" width="30" height="40" behavior="alternate" >
<marquee behavior="alternate">
<strike>:)</strike>
</marquee>
</marquee> )

<strike>Alan Elston</strike>
Click to expand...

_.................................

So, In the editor chop some bits out:

[QUOTE="DocAElstein, post: 4499209, member: 286762"][B]Just to complete my demo[/B] codes using ShumsFaruk solution way...
_...................................

[B]Following demo codes, for example working on this:[/B]
......
_......................................................
[B]and Now for[/B] ::::...”....[I]. to refer from which row to last column[/I]……“
.....
[B]These codes:[/B]
......[B]Gives:[/B]
..........
_....................
[/QUOTE]
 
Last edited:
Upvote 0
Re: Methods to Modify Code to use Last Row and Last Column rather than Hard Coding

Still Learning :p:)
P.s. Just quote a little . – That helps to keep the Thread a bit more readable :)
Click to expand...
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Solved
formating question
Replies
3
Views
66
Micron
M
L
  • Locked
  • Question
Help Cleaning Up Code
Replies
2
Views
321
RoryA
RoryA
K
  • Solved
create a button that do actions
2 3
Replies
22
Views
847
kerm007
K
F
  • Solved
Looping code sequence
Replies
5
Views
140
FrenchCelt
F
P
  • Question
Issue with button not running in spread sheet only in test mode
Replies
1
Views
181
6StringJazzer
6StringJazzer

Forum statistics

Threads
1,214,647
Messages
6,120,722
Members
448,987
Latest member
marion_davis

Share this page

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
Back
Top