Help with VBA to change range limits

EssKayKay

Active Member
Joined
Jan 5, 2003
Messages
402
Office Version
  1. 2007
Platform
  1. Windows
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.
 
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.
 
Upvote 0

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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. . .
 
Upvote 0
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;"
 
Upvote 0
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
 
Upvote 0
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

VBA Code:
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.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top