Last Column With Data
in [Programming]
|
From: Bishop on 5 May 2010 13:01 How do I check a certain row to find out the last column in the row that has data?
From: Dave Peterson on 5 May 2010 13:17 Dim myRow as long dim LastCol as long myrow = 1243 with worksheets("SomeSheetnamehere") lastcol = .cells(myrow,.columns.count).end(xltoleft).column end with msgbox LastCol This will break if you've used the last column in that row -- or if you haven't used any cells in that row. Is that a problem? Bishop wrote: > > How do I check a certain row to find out the last column in the row that has > data? -- Dave Peterson
From: Bishop on 5 May 2010 15:07 This gives a interger value but I can't make it work with this statement: ..Columns("B:" & LastCol).EntireColumn.AutoFit Do I need to set LastCol as something other than Long? Or is there a different syntax I should use to make the above statement work? "Dave Peterson" wrote: > Dim myRow as long > dim LastCol as long > > myrow = 1243 > > with worksheets("SomeSheetnamehere") > lastcol = .cells(myrow,.columns.count).end(xltoleft).column > end with > > msgbox LastCol > > This will break if you've used the last column in that row -- or if you haven't > used any cells in that row. > > Is that a problem? > > > > Bishop wrote: > > > > How do I check a certain row to find out the last column in the row that has > > data? > > -- > > Dave Peterson > . >
From: Rick Rothstein on 5 May 2010 15:16 Try changing this line... lastcol = .cells(myrow,.columns.count).end(xltoleft).column to this... lastcol = split(.cells(myrow,.columns.count).end(xltoleft).address, "$")(1) and see if that works for you. -- Rick (MVP - Excel) "Bishop" <Bishop(a)discussions.microsoft.com> wrote in message news:065D91F9-F491-40EF-8C79-4A82C4936A66(a)microsoft.com... > This gives a interger value but I can't make it work with this statement: > > .Columns("B:" & LastCol).EntireColumn.AutoFit > > Do I need to set LastCol as something other than Long? Or is there a > different syntax I should use to make the above statement work? > > "Dave Peterson" wrote: > >> Dim myRow as long >> dim LastCol as long >> >> myrow = 1243 >> >> with worksheets("SomeSheetnamehere") >> lastcol = .cells(myrow,.columns.count).end(xltoleft).column >> end with >> >> msgbox LastCol >> >> This will break if you've used the last column in that row -- or if you >> haven't >> used any cells in that row. >> >> Is that a problem? >> >> >> >> Bishop wrote: >> > >> > How do I check a certain row to find out the last column in the row >> > that has >> > data? >> >> -- >> >> Dave Peterson >> . >>
From: Dave Peterson on 5 May 2010 15:51
You could use: ..range("b1",.cells(1,lastcol)).entirecolumn.Autofit or ..range("b1",.columns(lastcol)).entirecolumn.autofit Bishop wrote: > > This gives a interger value but I can't make it work with this statement: > > .Columns("B:" & LastCol).EntireColumn.AutoFit > > Do I need to set LastCol as something other than Long? Or is there a > different syntax I should use to make the above statement work? > > "Dave Peterson" wrote: > > > Dim myRow as long > > dim LastCol as long > > > > myrow = 1243 > > > > with worksheets("SomeSheetnamehere") > > lastcol = .cells(myrow,.columns.count).end(xltoleft).column > > end with > > > > msgbox LastCol > > > > This will break if you've used the last column in that row -- or if you haven't > > used any cells in that row. > > > > Is that a problem? > > > > > > > > Bishop wrote: > > > > > > How do I check a certain row to find out the last column in the row that has > > > data? > > > > -- > > > > Dave Peterson > > . > > -- Dave Peterson |