"Reset" Lastcell

[icegg]

My table is in A1:H20 and I use "Cells.SpecialCells(xlCellTypeLastCell)" in the macro. However, if I accidentally type something after row 20 or column H and I cannot use Undo anymore, the last cell has changed. Deleting does not help.
Is there a way to assign or reset the last cell to H20?
Thanks a lot.

[Bob Phillips]

See http://www.contextures.com/xlfaqApp.html#Unused

[lucas]

You might use a named range....
post your code and/or workbook please

[icegg]

See http://www.contextures.com/xlfaqApp.html#Unused

Got it. The key is: after deleting unwanted cells, save, close and re-open.:thumb

[icegg]

You might use a named range....
post your code and/or workbook please

To be honest, I do not know how to use a named range. I just started learning to use VBA by reading Excel Help and articles from ozgid, google and especially this forum:thumb where I can ask silly questions and get good answers. :thumb

It is a button on a userform. I just want it to do something with cells in column 1,5 and 12 from row 15 to the last row that might be changing during revision.


Private Sub CalculateButton_Click()
Set r = ActiveSheet.Range(Cells(15, 1), Cells.SpecialCells(xlCellTypeLastCell))
For n = 1 To r.Rows.Count
If IsEmpty(Cells(14 + n, 1).Value) Or IsError(Cells(14 + n, 1).Value) Then
Cells(14 + n, 1).Formula = "=a" & 13 + n: Cells(14 + n, 1).Font.ColorIndex = 2
Cells(14 + n, 1).Formula = "=a" & 13 + n: Cells(14 + n, 1).Font.ColorIndex = 2: Cells(14 + n, 1).ShrinkToFit = True: Cells(14 + n, 1).WrapText = False
End If
If IsEmpty(Cells(14 + n, 5).Value) Then
Cells(14 + n, 5).Formula = "=e" & 13 + n
ElseIf IsError(Cells(14 + n, 5).Value) Then
Cells(14 + n, 5).Formula = "=e" & 13 + n
End If
Cells(14 + n, 12).Formula = "=e" & 14 + n & "*h" & 14 + n & "*k" & 14 + n
Next
Me.Hide
End Sub

[mdmackillop]

Hi icegg,
I've not tried yet to foillow your loigic, just some tips on presentation

Use Option Explicit and declare all your variables.
A colon joins two lines of code into one. This does not make other than very short lines easier to understand.
Repeated objects are better replaced by a with statement.
Lines 5 & 6 of your code contain identical instructions
If you are goint to use single letter variables (as opposed to meaningful names) and cell references, why not use capitals to distinguish them

Here is your code rewritten; no changes in its function. Can you explain what you are tying to achieve

Option Explicit

Private Sub CalculateButton_Click()
Dim r As Range, n As Long
Set r = ActiveSheet.Range(Cells(15, 1), Cells.SpecialCells(xlCellTypeLastCell))
For n = 1 To r.Rows.Count
With Cells(14 + n, 1)
If IsEmpty(.Value) Or IsError(.Value) Then
.Formula = "=A" & 13 + n
.Font.ColorIndex = 2
.ShrinkToFit = True
.WrapText = False
End If
End With
With Cells(14 + n, 5)
If IsEmpty(.Value) Then
.Formula = "=E" & 13 + n
ElseIf IsError(.Value) Then
.Formula = "=E" & 13 + n
End If
End With
Cells(14 + n, 12).Formula = "=E" & 14 + n & "*H" & 14 + n & "*K" & 14 + n
Next
Me.Hide
End Sub

[icegg]

Man, doesn't it look much neater.:thumb I have replaced my scrap with your code. Also, Thanks much for your comments. :clap:

Now, let me try explaining what I want to do....
This code is supposed to 1) look for empty or error cells in column 1 and 5 from row 15 to the end of the table, 2) assign a formula into each one making it equals to the cell above it and 3)put a formula into every cell in column 12 from row 15 to the end of the table.

It really took me some time to figure out how to set the range using the last cell. But, the end of the table is changing when I add or delete rows. That is why I posted the question .

[mdmackillop]

Slightly different metholdology
Private Sub CalculateButton_Click()
Dim r As Range, n As Long
Dim cel As Range, Rw As Long
Set r = ActiveSheet.Range(Cells(15, 1), Cells(Rows.Count, 1).End(xlUp))
For Each cel In r
With cel
If IsEmpty(.Value) Or IsError(.Value) Then
.Formula = "=" & cel.Offset(-1).Address(0, 0)
.Font.ColorIndex = 2
.Interior.ColorIndex = 5
.ShrinkToFit = True
.WrapText = False
End If
End With
With cel.Offset(, 4)
If IsEmpty(.Value) Then
.Formula = "=" & cel.Offset(-1, 4).Address(0, 0)
.Interior.ColorIndex = 6
ElseIf IsError(.Value) Then
.Formula = "=" & cel.Offset(-1, 4).Address(0, 0)
.Interior.ColorIndex = 7
End If
End With
Rw = cel.Row
cel.Offset(, 11).Formula = "=E" & Rw & "*H" & Rw & "*K" & Rw
Next
'Me.Hide
End Sub

[icegg]

Thanks man. I didn't know there was an "Offset" method.