dynamic range and named rangesSolved/Closed

Question

Hi,

I have a list of students in Sheet 1,2,3 where being updated regularly. They are named ranges "Boys" & "Girls" in Column A & B respectively. In Sheet 4, I have two named ranges "Class1" & "Class2" lined up in Column B which they are separated with few number of rows and headers in between them. 

I have a drop down list where i can select from Sheet 1,2,3 and get the first row reference on each column and autofill them on the named ranges "Class1 & "Class2" respectively (see vb code below). 

Private Sub CommandButton1_Click()

Range("ClassB") = "='" & ComboBox1.Text & "'!A5"
Range("ClassB").Select
Selection.AutoFill Destination:=Range("Class1"), Type:=xlFillValues
Range("ClassG") = "='" & ComboBox1.Text & "'!B5"
Range("ClassG").Select
Selection.AutoFill Destination:=Range("Class2"), Type:=xlFillValues
Range("ClassB").Select
Application.CutCopyMode = False


End Sub

The problem is I need to adjust/insert the corresponding number of rows for each field manually. Is there a way to expand or contract the rows within the named ranges in Sheet 4 automatically based on the number of data that goes in respect of # rows (number of students)? I tried copy paste but the rows stays the same. Dynamic Ranges seem ok but not with two named ranges line up in one column coz it will only over write other data between the two named ranges lined up in vertical. 


I hope I did not confused you. Pardon my English and sorry for the long story. Please help.

TrowaD · Answer

Hi Iloco,

So you want to automatically change your named ranges when more students are added? or did you confuse me?

Best regards,
Trowa

iloco · Answer

Hi Trowa,

Just the rows in Sheet 4 named ranges to expand and contract automatically to accommodate the students (list are increasing/decreasing regularly). Sort of  auto insert rows and delete rows based on the number of rows of students.


Tks

TrowaD · Answer

Hi Iloco,

So count the students:
Sheet1 Column A Row2 until last entry +
Sheet1 Column B Row2 until last entry +
Same for sheet 2 and 3.

Then in sheet 4 ungroup all rows and create a new group based on the student count starting at row2.

Am I close or are my brains failing me.

Showing what you want in a sample file will make things easier to understand (Using a filesharing site like www.speedyshare.com).

Best regards,
Trowa

iloco · Answer

Hi Trowa,

Here is the download link:

http://speedy.sh/2wZXa/Class-Record-2012.xlsm

It is a grading system for elementary school intended for my wife. Anyhow, in the "Menu" ...navigate on "First Grading" and then click on any of those number....say # 1. Hence, from there you'll find column B (list of students) and just by the title there is a button "change name" (Yellow). Also, you'll find in this column the two named ranges I mentioned. Back to the Menu you'll find "Name of Pupils" to edit the names/list, this is where I get the data automatically.

I hope not to confuse you this time.

Thanks for giving time for this, appreciate much.

iloco · Answer

Hi Trowa,

Sorry for the late reply...been away lately.

Anyhow, I managed to work on some codes (copied somewhere) that is almost closed to it. Need to say it is not yet fully working as I have expected. The codes I want should be only adjusting the rows equally the same from the source within those nameD ranges. Hence, I have another codes to autofill it with the contents from the same source (this code is ok and i can make it work).

Here is where I am getting that idea to adjust the rows:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim listRows As Integer, ganttRows As Integer, listRange As Range, ganttRange As Range
    Dim wks1 As Worksheet, wks2 As Worksheet

    Set wks1 = Worksheets("Sheet2")
    Set wks2 = Worksheets("Sheet1")

    Set listRange = Intersect(wks1.UsedRange, wks1.columns("B:B").EntireColumn)
    Set ganttRange = Intersect(wks2.UsedRange, wks2.columns("B:B").EntireColumn)

    listRows = listRange.Rows.count
    ganttRows = ganttRange.Rows.count

    If listRows > ganttRows Then 'sheet 1 has more rows, need to insert
        wks1.Range(wks1.Cells(listRows - (listRows - ganttRows), 1), wks1.Cells(listRows, 1)).EntireRow.Copy 
       wks2.Cells(ganttRows, 1).offset(1).PasteSpecial xlPasteValues
    ElseIf ganttRows > listRows 'sheet 2 has more rows need to delete
        wks2.Range(wks2.Cells(ganttRows, 1), wks2.Cells(ganttRows - (ganttRows - listRows), 1)).EntireRow.Delete
    End If

    Dim cel As Range
    'reset range because of updates
    Set ganttRange = Intersect(wks2.UsedRange, wks2.columns("B:B").EntireColumn)

    For Each cel In ganttRange
        If cel.Row Mod 2 = 0 Then cel.EntireRow.Interior.ColorIndex = 20
    Next

End Sub


As you can see it is actually restructuring the whole rows being copied and transferred to the other sheet. What i want is just the number of rows to be adjusted not its content. 

Brgds, 
iloco

TrowaD · Answer

Hi Iloco,

So when Sheet1 has more rows then Sheet2 then you want to add the row difference to Sheet1? Making the row difference twice as big?

The rows you want to copy over needs to be blank. Aren't the rows below your used range already blank?

Best regards,
Trowa

iloco · Answer

Hi Trowa,

I sort it out already. I just need to max out more rows in sheet 2 say to 50 rows which I believe the list will be more than that. Hence, then I all I need to do is hide the rows automatically using vba filter for zero values. I think it will be less complicated for me who gets messed up with VBA.

Thanks for the assistance. I will probably go back with it in the future and ask for for guidance. Jolly good mate. Tks

brgds,
Iloco

Dynamic range and named ranges

7 responses

Similar discussions

Subscribe To Our Newsletter!