last non-blank cell definition for plain excel user...

[driller]

Hi all,

Sometime, when try to insert a row in my sheet, a pop-up says
"To prevent possible....,...cannot shift nonblank cells off the worksheet.
Try to locate the last nonblank cell by pressing CTRL+END,...."

I am just confused about previous post here where the emptycell are
discussed...

The way i see it, excel reads the nonblank cells- regardless if it is empty-
as long as it is not on a virgin state...meaning if it is already formatted
by the user, color fills or border...I have no problem with that...

But observe this, try to make a conditional format in IV65536, something
like cell value is:="", color pattern :green...leave the cell empty...
then ctrl+home, ctrl+end...
you may see the difference..it is not acknowledge as the last nonblank cell,
whereas other cell with a color pattern made manually can be read by the
ctrl-end...

I wonder if the recorded test macro i did will explain it in your own
sheet....or maybe myexcel is not running properly...

please explain/advice...how to specify in the forum about *looking for last
non-blank cell* related to worksheet function...

I tried this in a *virgin sheet*
-------
Sub Macro1()
'
' Macro1 Macro
' Macro recorded 7/16/2007 by Romel
'
' Keyboard Shortcut: Ctrl+e
'
Columns("m:m").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
' With Selection.Borders(xlInsideVertical)
' .LineStyle = xlContinuous
' .Weight = xlThin
' .ColorIndex = xlAutomatic
' End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Range("F1").Select
Selection.End(xlDown).Select
Range("F65535").Select
ActiveCell.FormulaR1C1 = "2"
Range("A1").Select
ActiveWorkbook.Save
ActiveCell.SpecialCells(xlLastCell).Select
Range("P65536").Select
With Selection.Interior
.ColorIndex = 44
.Pattern = xlSolid
End With
Range("O65535").Select
ActiveCell.FormulaR1C1 = "2"
Range("A1").Select
ActiveWorkbook.Save
ActiveCell.SpecialCells(xlLastCell).Select
Range("R65536").Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="="""""
Selection.FormatConditions(1).Interior.ColorIndex = 3
Range("A1").Select
ActiveWorkbook.Save
ActiveCell.SpecialCells(xlLastCell).Select
End Sub