Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Deleting rows and updating dynamic range

  Asked By: Archie    Date: Feb 09    Category: MS Office    Views: 1120
  

I want to make a form with a combo box generated from a dynamic range
which will give a list of items, then be able from a command button to
delete a selected item.

I have a problem in generating the list of items after one is deleted,
the range does not update.

I would appreciate some guidance in solving this problem.

Share: 

 

6 Answers Found

 
Answer #1    Answered By: Waggoner Fischer     Answered On: Feb 09

I don't any books in front of me now to see if I am correct, but are you
using obj.update after you delete the items

 
Answer #2    Answered By: Davi Costa     Answered On: Feb 09

No, I am trying to recalculate the range. Here are the codes.

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
Dim mylastRow, mylastColumn, mylastCell, myRange As range


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 =
Worksheets("EnterCowData").range("A65536").End(xlUp).Select
If WorksheetFunction.CountA(Cells) > 0 Then
'Search for any entry, by searching backwards by Rows.
CmyLastARow = Cells.Find(What:="*", After:=[A1], _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row
MsgBox CmyLastARow
End If


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
'reset the range

Nothing execute here

End If

End Sub

'function to call values
Private Sub LoadRow()
Worksheets("EnterCowData").Cells(k, 0).Value =
Worksheets("EnterCowData").Cells((k + 1), 0).Value

End Sub

 
Answer #3    Answered By: Sydney Thompson     Answered On: Feb 09

I took a quick look. If I understand it correctly, it seems that you
are loading the combo  box in the form_initialize subroutine and that you
need to run the same code you run there at the end of the subroutine
cmdProblemDelete. I would put the code in the form_initialize
subroutine in a separate subroutine and call it from both
form_initialize and cmdproblemdelete.

 
Answer #4    Answered By: Kim Cruz     Answered On: Feb 09

I tried your suggestion, but the range  is still
not refreshed after I deleted  the row. Do you have any ideas? Here are
my codes for the subroutine called RangeSelection() and my form.
'Range Selection
Sub RangeSelection()
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"
End Sub

Private Sub UserForm_initialize()
Call RangeSelection
cboCowList.RowSource = "Options"
cboCowList.BoundColumn = 1
cboCowList.ColumnCount = 1
Application.ScreenUpdating = True
End Sub

Private Sub cboCowList_Click()
Worksheets("EnterCowData").Activate
CmyLastRow = LastCell(Worksheets("EnterCowData")). Row My problem
is here after I deleted a specific row.
CmyRange = "A3: A" & CmyLastRow
Set CowListStart = Worksheets("EnterCowData").range("A3")
CowID = cboCowList.Value
i = 0
Do Until i = CmyLastRow + 1
If CowListStart.Offset(i, 0).Value = CowID Then
DeleteRange = "A" & CowListStart.Offset(i, 0).Row
End If
i = i + 1
Loop
End Sub
Private Sub cmdProblemDelete_Click()
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
'reset the range
Call RangeSelection
End If

End Sub

 
Answer #5    Answered By: Adelbert Fischer     Answered On: Feb 09

I started with the code you posted, and ended up with the following:

Option Explicit
Public CmyLastRow As Long, CmyRange As String
Public CowListStart As Range, DeleteRange As Long

Private Sub cmdProblemDelete_Click()
Dim msg As String, DelRow As Long
'Make sure a cow ID has been selected.
If Me.cboCowList.ListIndex = -1 Then
MsgBox "You must select the cow ID to be deleted", vbExclamation,
"cmdProblemDelete_Click"
Exit Sub
End If
'Confirm the deletion.
msg$ = "Are you sure you want to delete cow " & Me.cboCowList.Value & "?"
If MsgBox(msg$, vbQuestion + vbYesNo, "Confirm Delete") = vbYes Then
'Delete the row for the selected  cow.
Worksheets("EnterCowData").Activate
'Since cow IDs are sorted and are in the same order as in the combobox, we can
calculate
'the correct row. ListIndex is the position of the selected item  in the comcobox
list. Since
'it begins numbering with zero, we must add 1. Also add 3 for the rows  above the
first cow.
DelRow& = (Me.cboCowList.ListIndex + 1) + 3
ActiveSheet.Cells(DelRow&, 1).EntireRow.Delete
'Reload the combobox.
Call LoadcboCowList
End If
End Sub

Private Sub LoadcboCowList()
'Call RangeSelection to sort the data and assign value to CmyLastRow.
Call RangeSelection
cboCowList.RowSource = "A4:A" & CmyLastRow
cboCowList.BoundColumn = 1
cboCowList.ColumnCount = 1
'No item selected, to start.
cboCowList.ListIndex = -1
End Sub

Private Sub RangeSelection()
'Finds the used range, sorts the data, and populates public variables.
Dim TmpAddr As String
Worksheets("EnterCowData").Activate
'Call FindLastCell function to get the address of the cell with the highest used
'column and highest used row on the selected sheet.
TmpAddr = FindLastCell(Worksheets("EnterCowData"))
'If FindLastCell returned "ERROR" the sheet is probably blank.
If TmpAddr$ = "ERROR" Then Exit Sub
'Assign the last used row to CmyLastRow
CmyLastRow = Range(TmpAddr$).Row
'Select all the cells from A3 through TempAddr
CmyRange = "A3:" & TmpAddr$
'Sort the range  by CowID
Range(CmyRange).Select
Selection.Sort Key1:=Range("A3"), Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End Sub

Private Sub UserForm_initialize()
Call LoadcboCowList
Application.ScreenUpdating = True
End Sub

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

I'm afraid I have altered it quite a bit, but it does work reliably now to
load cboCowList, delete the selected cow, and reload cboCowList.

If these changes interfere with something else in your project, let me know,
and i will be glad to help you.

 
Answer #6    Answered By: Bian Nguyen     Answered On: Feb 09

Thank you for your help. Your coding is more elaborated than mine, I will try
them, I am still a beginner.

Last night I finally solved it. Here are the working codes:

Private Sub UserForm_initialize()
Call RangeSelection
cboDeleteCow.RowSource = "Options"
cboDeleteCow.BoundColumn = 1
cboDeleteCow.ColumnCount = 1
Worksheets("EnterCowData").Activate
CmyLastRow = LastCell(Worksheets("EnterCowData")).Row
CmyRange = "A3: A" & CmyLastRow
Application.ScreenUpdating = True

End Sub


Private Sub cboDeleteCow_Click()

Set CowListStart = Worksheets("EnterCowData").range("A3")
CowID = cboDeleteCow.Value
i = 0
Do Until i = CmyLastRow + 1
If CowListStart.Offset(i, 0).Value = CowID Then
DeleteRange = "A" & CowListStart.Offset(i, 0).Row
End If
i = i + 1
Loop
End Sub

Private Sub cmdProblemDelete_Click()
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

'reset the range
Call RangeSelection
End Sub

 
Didn't find what you were looking for? Find more on Deleting rows and updating dynamic range Or get search suggestion and latest updates.




Tagged: