Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a List Within Rows...Not Columns
In Column "AT" (46) I have seed companies listed starting in row 2 on down
with a count of the number of seed companies in row 1. In Column "AS" (45) I have a count of the number of hybrids for each seed company with the hybrids listed, next to the appropriate seed company, in Column "AU" (47) on to the right. What I want to do is be able to remove a hybrid selected by the user. What I have is a combobox (cboSSeedCo2) with a list of the seed companies, once a company is selected I want a second combobox (cboSHybrid) to populate with a list of the hybrids for that company. The first set of code below is located in the "Sheet Code" and is used to populate cboSHybrid, which I cannot get to work properly. The second set of code is used to delete the selected hybrid and shift all other hybrids that are to the right of the deleted hybrid to the left...therefore creating a row (horizontal) list with no blank cells. For whatever reason I cannot get this to work properly and the code below is all I need to work. Are there any recommendations on how to fix the code below to work that way I have outlined above? Any help would be greatly appreciated! Private Sub cboSSeedCo2_Change() Dim i, iEnd, iHybrid As Integer i = Range("at1") For Counter = 1 To i + 1 Set curcell = ActiveSheet.Cells(Counter, 46) If curcell = Range("aa20") Then iHybrid = Cells(Counter, 45) iEnd = iHybrid + 46 ActiveSheet.cboSHybrid.ListFillRange = Range(Cells(Counter, 47) & ":" & Cells(Counter, iEnd)) End If Next Counter End Sub Sub RemoveHybrid() Dim i, j, val As Integer i = Range("at1") For Counter = 1 To i + 1 Set curcell = ActiveSheet.Cells(Counter, 46) If curcell = Range("aa20") Then val = Counter End If Next Counter j = Range("as" & val) For Counter = 47 To j + 46 Set curcell = ActiveSheet.Cells(val, 47) If curcell = Range("aa21") Then ActiveSheet.Cells(val, 47 + Range("as" & Counter)).Delete Shift:=xlShiftLeft Worksheets("Index").Range("au" & val & ":iv" & val).Sort Key1:=Worksheets("Index").Range("au" & val) ActiveSheet.Range("as" & val) = ActiveSheet.Range("as" & val) - 1 End If Next Counter End Sub -- Tyrell Fickenscher Plant Manager / Agronomist |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
create 1 master list from a combination of rows and columns | Excel Discussion (Misc queries) | |||
Creating Columns from space delimited list | Excel Discussion (Misc queries) | |||
How do I display a list of the rows or columns that I've hidden? | Excel Discussion (Misc queries) | |||
Change a Column list into multiple rows & columns | Excel Worksheet Functions | |||
creating columns with data from offset rows | Excel Discussion (Misc queries) |