desantech
07-07-2018, 10:12 PM
Hello
I want to ask you ist there a way to show this line lets say:
Range("O1:O" & .Cells(.Rows.Count, "O").End(xlUp).Row).SpecialCells(xlCellTypeConstants)
In variables?
(My worksheet has some rows and in last column "O" there are info, header go only to Column "N")
I have tried this, and it works, but looks very like putting puzzles together:
Dim LastColumn As Long
Set sht = ThisWorkbook.Worksheets("temp3")
LastColumn = sht.UsedRange.Columns(sht.UsedRange.Columns.Count).Column - 1 'I had to put in "-1" else it would put me to Column "P" not "O"
With Sheets("Temp3")
Dim lastCol$
'lastCol = Split(Columns(Range("A1").End(xlToRight).Column).Address(, False), ":")(1) 'this also works but gets me to column "N" not "O" – because 1. Row is only till N, so I dont use it and use this better:
lastCol = Split(Sheets("temp3").Cells(, LastColumn).Address, "$")(1) ' here I get the lastcolumnr as Letter
'Finally what I had to put together to have it work:
With .Range(lastCol & "1" & ":" & lastCol & .Cells(.Rows.Count, lastCol).End(xlUp).Row).SpecialCells(xlCellTypeConstants)
Its not important to answer, because it works, but any better solution (faster) would be nice to know
Thank you
22534
I want to ask you ist there a way to show this line lets say:
Range("O1:O" & .Cells(.Rows.Count, "O").End(xlUp).Row).SpecialCells(xlCellTypeConstants)
In variables?
(My worksheet has some rows and in last column "O" there are info, header go only to Column "N")
I have tried this, and it works, but looks very like putting puzzles together:
Dim LastColumn As Long
Set sht = ThisWorkbook.Worksheets("temp3")
LastColumn = sht.UsedRange.Columns(sht.UsedRange.Columns.Count).Column - 1 'I had to put in "-1" else it would put me to Column "P" not "O"
With Sheets("Temp3")
Dim lastCol$
'lastCol = Split(Columns(Range("A1").End(xlToRight).Column).Address(, False), ":")(1) 'this also works but gets me to column "N" not "O" – because 1. Row is only till N, so I dont use it and use this better:
lastCol = Split(Sheets("temp3").Cells(, LastColumn).Address, "$")(1) ' here I get the lastcolumnr as Letter
'Finally what I had to put together to have it work:
With .Range(lastCol & "1" & ":" & lastCol & .Cells(.Rows.Count, lastCol).End(xlUp).Row).SpecialCells(xlCellTypeConstants)
Its not important to answer, because it works, but any better solution (faster) would be nice to know
Thank you
22534