Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
SpecialCells(xlLastCell)
Hello Group, I have a problem with a macro, and maybe you can help me
I use a sentence 'ActiveCell.SpecialCells(xlLastCell).Select' to go to the last worksheet cell. The problemis when this cell has been erased. Then it's not really the last cell, but Excel still points to this one. Saving, closing and re-opening, then Excel points to the real last cell. Do you know how to refresh the xlLastCell without closing the book? Thanks in advance. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
SpecialCells(xlLastCell)
Hi Jose,
Using code to delete the row and then saving the file will change the last cell. Hope that helps. "José Ignacio Bella" wrote: Hello Group, I have a problem with a macro, and maybe you can help me I use a sentence 'ActiveCell.SpecialCells(xlLastCell).Select' to go to the last worksheet cell. The problemis when this cell has been erased. Then it's not really the last cell, but Excel still points to this one. Saving, closing and re-opening, then Excel points to the real last cell. Do you know how to refresh the xlLastCell without closing the book? Thanks in advance. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
SpecialCells(xlLastCell)
sometimes it's so easy... i'll try it.
thx "David" escribió en el mensaje ... Hi Jose, Using code to delete the row and then saving the file will change the last cell. Hope that helps. "José Ignacio Bella" wrote: Hello Group, I have a problem with a macro, and maybe you can help me I use a sentence 'ActiveCell.SpecialCells(xlLastCell).Select' to go to the last worksheet cell. The problemis when this cell has been erased. Then it's not really the last cell, but Excel still points to this one. Saving, closing and re-opening, then Excel points to the real last cell. Do you know how to refresh the xlLastCell without closing the book? Thanks in advance. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
SpecialCells(xlLastCell)
See http://www.contextures.com/xlfaqApp.html#Unused for a way to fix this in
code. -- HTH RP (remove nothere from the email address if mailing direct) "David" wrote in message ... Hi Jose, Using code to delete the row and then saving the file will change the last cell. Hope that helps. "José Ignacio Bella" wrote: Hello Group, I have a problem with a macro, and maybe you can help me I use a sentence 'ActiveCell.SpecialCells(xlLastCell).Select' to go to the last worksheet cell. The problemis when this cell has been erased. Then it's not really the last cell, but Excel still points to this one. Saving, closing and re-opening, then Excel points to the real last cell. Do you know how to refresh the xlLastCell without closing the book? Thanks in advance. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
SpecialCells(xlLastCell)
Not so easy
If you enter something in B21 and I3, the LastCell will be I21. So I cannot check for LastCell = empty, and then delete the row "José Ignacio Bella" escribió en el mensaje ... sometimes it's so easy... i'll try it. thx "David" escribió en el mensaje ... Hi Jose, Using code to delete the row and then saving the file will change the last cell. Hope that helps. "José Ignacio Bella" wrote: Hello Group, I have a problem with a macro, and maybe you can help me I use a sentence 'ActiveCell.SpecialCells(xlLastCell).Select' to go to the last worksheet cell. The problemis when this cell has been erased. Then it's not really the last cell, but Excel still points to this one. Saving, closing and re-opening, then Excel points to the real last cell. Do you know how to refresh the xlLastCell without closing the book? Thanks in advance. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
SpecialCells(xlLastCell)
Just a crazy idea (not fully tested). Should ignore any cells that do not
contain formulas or values (incl. text, numeric and logical). Regards, KL '------Code Start------- Function LastCell(Optional Ws As Worksheet) As Range Dim consts As Range: Dim frmls As Range Dim r As Single: Dim c As Integer Dim rTemp As Single: Dim cTemp As Integer Dim rng As Range If Ws Is Nothing Then Set Ws = ActiveSheet With Ws.Cells On Error Resume Next Set consts = .SpecialCells(xlCellTypeConstants) Set frmls = .SpecialCells(xlCellTypeFormulas) On Error GoTo 0 If Not frmls Is Nothing Then If Not consts Is Nothing Then Set rng = Application.Union(frmls, consts) Else Set rng = frmls End If ElseIf Not consts Is Nothing Then Set rng = consts End If End With r = 1: c = 1 For Each a In rng rTemp = a.Cells(a.Rows.Count, a.Columns.Count).Row cTemp = a.Cells(a.Rows.Count, a.Columns.Count).Column If rTemp r Then r = rTemp If cTemp c Then c = cTemp Next Set LastCell = Ws.Cells(r, c) End Function Sub test() Dim mysheet As Worksheet Dim myrng As Range Set mysheet = Sheets(2) Set myrng = LastCell(mysheet) mysheet.Activate myrng.Select End Sub '------Code End------- "José Ignacio Bella" wrote in message ... Hello Group, I have a problem with a macro, and maybe you can help me I use a sentence 'ActiveCell.SpecialCells(xlLastCell).Select' to go to the last worksheet cell. The problemis when this cell has been erased. Then it's not really the last cell, but Excel still points to this one. Saving, closing and re-opening, then Excel points to the real last cell. Do you know how to refresh the xlLastCell without closing the book? Thanks in advance. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
SpecialCells(xlLastCell)
Sorry, the line "For Each a In rng" should actually read "For Each a In
rng.Areas" KL "KL" wrote in message ... Just a crazy idea (not fully tested). Should ignore any cells that do not contain formulas or values (incl. text, numeric and logical). Regards, KL '------Code Start------- Function LastCell(Optional Ws As Worksheet) As Range Dim consts As Range: Dim frmls As Range Dim r As Single: Dim c As Integer Dim rTemp As Single: Dim cTemp As Integer Dim rng As Range If Ws Is Nothing Then Set Ws = ActiveSheet With Ws.Cells On Error Resume Next Set consts = .SpecialCells(xlCellTypeConstants) Set frmls = .SpecialCells(xlCellTypeFormulas) On Error GoTo 0 If Not frmls Is Nothing Then If Not consts Is Nothing Then Set rng = Application.Union(frmls, consts) Else Set rng = frmls End If ElseIf Not consts Is Nothing Then Set rng = consts End If End With r = 1: c = 1 For Each a In rng rTemp = a.Cells(a.Rows.Count, a.Columns.Count).Row cTemp = a.Cells(a.Rows.Count, a.Columns.Count).Column If rTemp r Then r = rTemp If cTemp c Then c = cTemp Next Set LastCell = Ws.Cells(r, c) End Function Sub test() Dim mysheet As Worksheet Dim myrng As Range Set mysheet = Sheets(2) Set myrng = LastCell(mysheet) mysheet.Activate myrng.Select End Sub '------Code End------- "José Ignacio Bella" wrote in message ... Hello Group, I have a problem with a macro, and maybe you can help me I use a sentence 'ActiveCell.SpecialCells(xlLastCell).Select' to go to the last worksheet cell. The problemis when this cell has been erased. Then it's not really the last cell, but Excel still points to this one. Saving, closing and re-opening, then Excel points to the real last cell. Do you know how to refresh the xlLastCell without closing the book? Thanks in advance. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
SpecialCells(xlLastCell)
Jose,
This function may do what you want. Copy into a module and run "Test_The_Function" below (You may need to correct for line wrapping): Public Function CellLastFilled() As String 'RETURN ADDRESS OF LAST FILLED CELL IN THE ACTIVE SHEET: ELSE 0; Dim intCol As Integer Dim lngRow As Long On Error Resume Next lngRow = ActiveSheet.UsedRange.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row intCol = ActiveSheet.Range(lngRow & ":" & lngRow).Find(What:="*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column CellLastFilled = IIf(Err 0, 0, Application.ConvertFormula("R" & lngRow & "C" & intCol, xlR1C1, xlA1)) On Error GoTo 0 End Function Sub Test_The_Function() MsgBox CellLastFilled End Sub "José Ignacio Bella" wrote: Hello Group, I have a problem with a macro, and maybe you can help me I use a sentence 'ActiveCell.SpecialCells(xlLastCell).Select' to go to the last worksheet cell. The problemis when this cell has been erased. Then it's not really the last cell, but Excel still points to this one. Saving, closing and re-opening, then Excel points to the real last cell. Do you know how to refresh the xlLastCell without closing the book? Thanks in advance. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
SpecialCells(xlLastCell)
Like I said before - crazy idea. After some testing, it looks like the
function defaults to the last cell of the sheet after certain size of rango or number of areas. The following code I found via Google (slightly modified) does seem to work: Function LastCell(Optional ws As Worksheet) As Range If ws Is Nothing Then Set ws = ActiveSheet Set rng = ws.Cells Set LastCell = rng(1) On Error Resume Next Set LastCell = Intersect( _ rng.Find("*", rng(1), xlValues, xlWhole, xlByRows, _ xlPrevious).EntireRow, _ rng.Find("*", rng(1), xlValues, xlWhole, xlByColumns, _ xlPrevious).EntireColumn) End Function KL "KL" wrote in message ... Sorry, the line "For Each a In rng" should actually read "For Each a In rng.Areas" KL "KL" wrote in message ... Just a crazy idea (not fully tested). Should ignore any cells that do not contain formulas or values (incl. text, numeric and logical). Regards, KL '------Code Start------- Function LastCell(Optional Ws As Worksheet) As Range Dim consts As Range: Dim frmls As Range Dim r As Single: Dim c As Integer Dim rTemp As Single: Dim cTemp As Integer Dim rng As Range If Ws Is Nothing Then Set Ws = ActiveSheet With Ws.Cells On Error Resume Next Set consts = .SpecialCells(xlCellTypeConstants) Set frmls = .SpecialCells(xlCellTypeFormulas) On Error GoTo 0 If Not frmls Is Nothing Then If Not consts Is Nothing Then Set rng = Application.Union(frmls, consts) Else Set rng = frmls End If ElseIf Not consts Is Nothing Then Set rng = consts End If End With r = 1: c = 1 For Each a In rng rTemp = a.Cells(a.Rows.Count, a.Columns.Count).Row cTemp = a.Cells(a.Rows.Count, a.Columns.Count).Column If rTemp r Then r = rTemp If cTemp c Then c = cTemp Next Set LastCell = Ws.Cells(r, c) End Function Sub test() Dim mysheet As Worksheet Dim myrng As Range Set mysheet = Sheets(2) Set myrng = LastCell(mysheet) mysheet.Activate myrng.Select End Sub '------Code End------- "José Ignacio Bella" wrote in message ... Hello Group, I have a problem with a macro, and maybe you can help me I use a sentence 'ActiveCell.SpecialCells(xlLastCell).Select' to go to the last worksheet cell. The problemis when this cell has been erased. Then it's not really the last cell, but Excel still points to this one. Saving, closing and re-opening, then Excel points to the real last cell. Do you know how to refresh the xlLastCell without closing the book? Thanks in advance. |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
SpecialCells(xlLastCell)
Dim CountData As Long
'If you are looking for the last cell in column 'B' CountData = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row Cells(CountData, 2).Select HTH--Lonnie M. |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
SpecialCells(xlLastCell)
Hi KL,
Hi KL, Like I said before - crazy idea. After some testing, it looks like the function defaults to the last cell of the sheet after certain size of rango or number of areas. See Knowledgebase article # 832293: The .SpecialCells(xlCellTypeBlanks) VBA function does not work as expected in Excel http://support.microsoft.com/default...b;en-us;832293 --- Regards, Norman "KL" wrote in message ... Like I said before - crazy idea. After some testing, it looks like the function defaults to the last cell of the sheet after certain size of rango or number of areas. The following code I found via Google (slightly modified) does seem to work: Function LastCell(Optional ws As Worksheet) As Range If ws Is Nothing Then Set ws = ActiveSheet Set rng = ws.Cells Set LastCell = rng(1) On Error Resume Next Set LastCell = Intersect( _ rng.Find("*", rng(1), xlValues, xlWhole, xlByRows, _ xlPrevious).EntireRow, _ rng.Find("*", rng(1), xlValues, xlWhole, xlByColumns, _ xlPrevious).EntireColumn) End Function KL |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
SpecialCells(xlLastCell)
Thanks Norman, one more thing learnt today.
Regards, KL "Norman Jones" wrote in message ... Hi KL, Hi KL, Like I said before - crazy idea. After some testing, it looks like the function defaults to the last cell of the sheet after certain size of rango or number of areas. See Knowledgebase article # 832293: The .SpecialCells(xlCellTypeBlanks) VBA function does not work as expected in Excel http://support.microsoft.com/default...b;en-us;832293 --- Regards, Norman "KL" wrote in message ... Like I said before - crazy idea. After some testing, it looks like the function defaults to the last cell of the sheet after certain size of rango or number of areas. The following code I found via Google (slightly modified) does seem to work: Function LastCell(Optional ws As Worksheet) As Range If ws Is Nothing Then Set ws = ActiveSheet Set rng = ws.Cells Set LastCell = rng(1) On Error Resume Next Set LastCell = Intersect( _ rng.Find("*", rng(1), xlValues, xlWhole, xlByRows, _ xlPrevious).EntireRow, _ rng.Find("*", rng(1), xlValues, xlWhole, xlByColumns, _ xlPrevious).EntireColumn) End Function KL |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
SpecialCells(xlLastCell)
Hello Quartz. Ok, my initial question was bad posed; first of all, I need to
define what means LastCell. Entering data in D9 and E7, your function says LastCell is D9 (cell with entry in greatest row) What I'm looking for is the last corner of the rectangle beginning with A1 and ending with the last used row and the last used column; in this case, LastCell is E9 (and doesn't matter if it's empty) KL sent me this code that works Function LastCell(Optional ws As Worksheet) As Range If ws Is Nothing Then Set ws = ActiveSheet Set Rng = ws.Cells Set LastCell = Rng(1) On Error Resume Next Set LastCell = Intersect( _ Rng.Find("*", Rng(1), xlValues, xlWhole, xlByRows, _ xlPrevious).EntireRow, _ Rng.Find("*", Rng(1), xlValues, xlWhole, xlByColumns, _ xlPrevious).EntireColumn) End Function Thanks to all again "quartz" escribió en el mensaje ... Jose, This function may do what you want. Copy into a module and run "Test_The_Function" below (You may need to correct for line wrapping): Public Function CellLastFilled() As String 'RETURN ADDRESS OF LAST FILLED CELL IN THE ACTIVE SHEET: ELSE 0; Dim intCol As Integer Dim lngRow As Long On Error Resume Next lngRow = ActiveSheet.UsedRange.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row intCol = ActiveSheet.Range(lngRow & ":" & lngRow).Find(What:="*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column CellLastFilled = IIf(Err 0, 0, Application.ConvertFormula("R" & lngRow & "C" & intCol, xlR1C1, xlA1)) On Error GoTo 0 End Function Sub Test_The_Function() MsgBox CellLastFilled End Sub "José Ignacio Bella" wrote: Hello Group, I have a problem with a macro, and maybe you can help me I use a sentence 'ActiveCell.SpecialCells(xlLastCell).Select' to go to the last worksheet cell. The problemis when this cell has been erased. Then it's not really the last cell, but Excel still points to this one. Saving, closing and re-opening, then Excel points to the real last cell. Do you know how to refresh the xlLastCell without closing the book? Thanks in advance. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cntrl + End (xlLastCell) | Excel Discussion (Misc queries) | |||
xllastcell vs xlcelltypelastcell | Excel Programming | |||
xlLastCell question | Excel Programming | |||
.SpecialCells(xlLastCell).Select | Excel Programming | |||
XP VBA: Range("A2", ActiveCell.SpecialCells(xlLastCell)).Select | Excel Programming |