View Full Version : Select range on Row until last empty cell
antoniosm
07-14-2016, 03:22 PM
Hi everyone,
I want to format the first line of a report I'm creating in VBA. Because the number of columns is variable I want to be able tp select the range from A1 until the last empty column row 1.
How can I do that on VBA?
Thanks
mancubus
07-14-2016, 10:45 PM
Dim LastCol As Long
LastCol = Cells(1, Columns.Count).End(xlToLeft).Column
Range(Cells(1, 1), Cells(1, LastCol)).WHATEVERYOULIKEFORFORMATHERE
'to fill the cells with yellow color, for instance:
Range(Cells(1, 1), Cells(1, LastCol)).Interior.Color = vbYellow
HimanshuK
07-14-2016, 11:17 PM
Dim sht As Worksheet
Dim LastColumn As Long
Set sht = ThisWorkbook.Worksheets("Sheet1")
'Ctrl + Shift + End
LastColumn = sht.Cells(7, sht.Columns.Count).End(xlToLeft).Column
'Using UsedRange
sht.UsedRange 'Refresh UsedRange
LastColumn = sht.UsedRange.Columns(sht.UsedRange.Columns.Count).Column
'Using Table Range
LastColumn = sht.ListObjects("Table1").Range.Columns.Count
'Using Named Range
LastColumn = sht.Range("MyNamedRange").Columns.Count
'Ctrl + Shift + Right (Range should be first cell in data set)
LastColumn = sht.Range("A1").CurrentRegion.Columns.Count
End Sub
Sub M_snb()
cells(1).currentregion.rows(1).interior.colorindex=15
End Sub
antoniosm
07-15-2016, 01:38 AM
HI thank you all for the answers, I'm trying to implement but it's not working I can't seem to fins the mistake..
' Format Report
lCol1 = Sheets(RName).Cells(1, Columns.Count).End(xlToLeft).Column
With Sheets(RName).Range(Cells(1, 1), Cells(R1, lCol1))
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.EntireColumn.ColumnWidth = 15
.WrapText = True
EndWith
With Sheets(RName).Range(Cells(1, 1), Cells(1, lCol1))
.Font.Bold = True
.Font.Size = 16
.Interior.ColorIndex = 37
EndWith
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.