MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

combo box, deleting a row

  Asked By: Jasmine    Date: Aug 26    Category: MS Office    Views: 1106

The following form is creating problem when I delete an entire row.
The combo box works fine when I open the form the first time, then
when I delete a row it should recalculate the range to populate the
combo box, but it does not. The procedure stops at this level
highlighted in red (CmyLastARow = LastCell(Worksheets
("EnterCowData")).Row ). I do not know what to do, do you have any



Option Explicit
Dim FirstAIDate As Variant
Dim SecondAIDate As Variant
Dim ThirdAIDate As Variant
Dim CmyLastRow, CmyProbRange, CmyLastCell, CmyLastARow As Variant
Dim DeleteRange As Variant
Dim CmyRange As Variant
Dim CowID As Integer
Dim CowListStart, CmyLastProbRow, CowListProbStart As range
Dim i, j, k As Integer
Dim CowStart As range
Dim message As Variant

Private Sub UserForm_initialize()


CmyLastRow = LastCell(Worksheets("EnterCowData")).Address
CmyRange = "A3:" & CmyLastRow

'sort the range by CowID
Selection.Sort Key1:=range("A3"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _

Worksheets("EnterCowData").range(CmyRange).Name = "Options"
cboCowList.RowSource = "Options"
cboCowList.BoundColumn = 1
cboCowList.ColumnCount = 1

Application.ScreenUpdating = True

End Sub

Private Sub cboCowList_Click()


CmyLastARow = LastCell(Worksheets("EnterCowData")).Row

CmyRange = "A3: A" & CmyLastARow
Set CowListStart = Worksheets("EnterCowData").range("A3")
CowID = cboCowList.Value
i = 0
Do Until i = CmyLastARow + 1
If CowListStart.Offset(i, 0).Value = CowID Then

k = i
End If
i = i + 1


End Sub
Private Sub cmdProblemDelete_Click()

CowID = cboCowList.Value
DeleteRange = "A" & CowListStart.Offset(k, 0).Row
message = "Are you sure you want to delete cow " & CowID & "?"

If MsgBox(message, vbQuestion + vbYesNo, _
"Confirm Delete") = vbYes Then
' Delete current row:


End If

End Sub



1 Answer Found

Answer #1    Answered By: Verner Fischer     Answered On: Aug 26

LastCell doesn't always update itself properly. You could try adding


just before the LastCell line, to make Excel recalculate what the last cell
is. If that doesn't work, here is a handy function which will find the last cell
with data on a worksheet:

Function FindLastCell(Wksht As Worksheet) As String
'Returns address of last cell used (highest row  & col) on specified sheet
Dim LastRow As Long
Dim LastCol As Integer
On Error GoTo FLCerr1
With Wksht
LastRow = 0
LastCol = 0
LastRow& = _
.Cells.Find("*", after:=.Cells(1), _
LookIn:=xlFormulas, LookAt:=xlWhole, _
SearchDirection:=xlPrevious, _
LastCol% = _
.Cells.Find("*", after:=.Cells(1), _
LookIn:=xlFormulas, LookAt:=xlWhole, _
SearchDirection:=xlPrevious, _
End With
FindLastCell$ = Cells(LastRow&, LastCol%).AddressLocal
Exit Function
FindLastCell$ = "ERROR"
End Function

You would call it like this:

CmyLastRow = FindLastCell(Sheets("EnterCowData"))

Please note, FindLastCell returns ERROR if the sheet is empty.

Didn't find what you were looking for? Find more on combo box, deleting a row Or get search suggestion and latest updates.