Help with VBA to change range limits

[EssKayKay]

Hello,

I have a dropdown list that is based on dates in a range named LookupDate. The range entry data is based on dates in column “C”, is one column wide, and changes length – but always starts at P33 such as Range(“P33:P200”).

I have a routine that adjusts this range accordingly which is working fine except for one minor issue. If an entry is made in a third column “I”, the corresponding cell in column “P” displays “----“.

Simplified example of formula in column P: IF(I33=0,C33," ----- ")

What’ s happening here is as numerous entries are made in column “I”, the dropdown list displays many “----“ options. Entries in column “I” will start in cell P33 and be consecutive with no rows missed. Therefore as time progresses, the beginning of the dropdown list starts with numerous “----“ entries. I was hoping to find a way to change the range to only include cells with dates.

Example: Cells “P33:P40” could have ---- entries with P41 displaying the first date. All cells between “P41:P200” would have dates. In this scenario, I would want the LookupDate range to be “P41:P200”. The beginning cell in the range would change as more entries are made.

Any suggestions would be appreciated.

Thanks for viewing,
Steve K.

 

[EssKayKay]

Could you please upload a sample workbook (without sensitive data) to a file-sharing site like Dropbox.com or Google Drive, and then share the link here? Also, ensure that the link is accessible to anyone.

I believe I have the link shared at Google Drive.
Thanks for the suggestion.

 

[EssKayKay]

Could you please upload a sample workbook (without sensitive data) to a file-sharing site like Dropbox.com or Google Drive, and then share the link here? Also, ensure that the link is accessible to anyone.

Hello Akuini,
I believe I have also uploaded my file to Dropbox (as well as Google Drive). Any suggestions would be greatly appreciated.

Tryme1.xlsm

Thanks again,
Steve

 

[Akuini]

Sorry for the delayed response. I've downloaded your file, but I'm a bit busy at the moment. I'll likely have time to review it in the next two days.

 

[Akuini]

I have a dropdown list that is based on dates in a range named LookupDate.

Where is the dropdown list located?

 

[EssKayKay]

Sorry for the delayed response. I've downloaded your file, but I'm a bit busy at the moment. I'll likely have time to review it in the next two days.

Absolutely not rush what so ever. I just wasn't sure my posts were available. Please continue with your other commitments as this project is not time sensitive in any way. I'm only playing.

 

[EssKayKay]

Where is the dropdown list located?

The dropdown list is in range named LookupDate - currently P33:P120, but that changes.

Again, as noted above, there is no rush on this. I am very pleased with you and all others on this site for the consideration for us "marginal" coders as I most certainly am.

Once more, my thanks. . .

 

[Akuini]

The dropdown list is in range named LookupDate - currently P33:P120, but that changes.

By dropdown list you mean data validation, right?
But I can't find any data validation in P33:P120, it just cells with formula =IF(@isformula(I33);C33;"

 

[EssKayKay]

By dropdown list you mean data validation, right?
But I can't find any data validation in P33:P120, it just cells with formula =IF(@isformula(I33);C33;"

Correct, for cells K8, K10, K12 -- Data Validation > Source = LookupDate

 

[EssKayKay]

I think I have this working. I found some coding on-line that was close to what I had in mind. I copied it and modified it a bit to meet my needs – so far it looks promising. I will keep working on this. If I run into any other issues (which is surely possible) I will be back.

Here’s my new subroutine

Private Sub ResetLookupDate()
Dim myLastRow As Long
Dim i As Long

     Application.ScreenUpdating = False
     myLastRow = Cells(Rows.Count, "M").End(xlUp).Row

     For i = 33 To myLastRow
         If Cells(i, "M").Value > 0 Then Range(Cells(i, "P"), Cells(i, "P")).ClearContents
     Next i

     Range("TempCell") = "X"
     Range("P" & Range("LastPmtRow").Value).Select
     Range(Selection, Selection.End(xlUp)).Select

     ActiveWorkbook.Names.Add Name:="LookupDate", RefersToR1C1:="=Amortize!R" & _
       Range("FirstLookupRow") & "C16:R" & Range("LastPmtRow") & "C16"

     Application.ScreenUpdating = True
End Sub

Here are the cells (range names) used in the routine above –
FirstLookupRow: =INDEX(B33:B777,MATCH(MIN(P33:P2000),P33:P2000,0))+32
LastPmtRow: =TotalPmts+32

I'm sure this coding is quite sloppy but it appears to be working. Any other suggestions to simplify etc. would be appreciated.
Again, thank you very much for understanding and support.