Logo 
Search:

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: 1144
  

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
hint?

Thanks.

Marie-Joelle

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()


Worksheets("EnterCowData").Activate

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

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


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

Application.ScreenUpdating = True

End Sub

Private Sub cboCowList_Click()

Worksheets("EnterCowData").Activate

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

Loop


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:
Worksheets("EnterCowData").Activate

range(DeleteRange).EntireRow.Delete

End If

End Sub

Share: 

 

1 Answer Found

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

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

Worksheets("EnterCowData").UsedRange

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, _
SearchOrder:=xlByRows).Row
LastCol% = _
.Cells.Find("*", after:=.Cells(1), _
LookIn:=xlFormulas, LookAt:=xlWhole, _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByColumns).Column
End With
FindLastCell$ = Cells(LastRow&, LastCol%).AddressLocal
Exit Function
FLCerr1:
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.




Tagged: