Dynamic range and named ranges

[Solved/Closed]
Report
Posts
5
Registration date
Monday October 29, 2012
Status
Member
Last seen
November 13, 2012
-
Posts
5
Registration date
Monday October 29, 2012
Status
Member
Last seen
November 13, 2012
-
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.

7 replies

Posts
2805
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 14, 2021
481
Hi Iloco,

So you want to automatically change your named ranges when more students are added? or did you confuse me?

Best regards,
Trowa
Posts
5
Registration date
Monday October 29, 2012
Status
Member
Last seen
November 13, 2012

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
Posts
2805
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 14, 2021
481
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
Posts
5
Registration date
Monday October 29, 2012
Status
Member
Last seen
November 13, 2012

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.
Posts
2805
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 14, 2021
481
Hi Iloco,

Sorry but can you re-upload your file with an .xls extention, since I'm still working in Excel 2003.

The "Menu" sheet is totally black for me, but what I understand now is that there is a list of students there. You want to move this list to the "English V - Dandelion" sheet and adjust the number of rows needed.
Example:
You now have 19 boys on the "English V - Dandelion" sheet .
A new list of students arive at "menu" sheet with 17 boys.
Move the list of 17 boys to the "English V - Dandelion" sheet and remove 2 rows.

This will mess up your print friendly format, so I can't be right, can I?

Sheet 1, 2 and 3 from your initial post are the vacant sheets?

Can you explain step by step what you want to achieve?

Best regards,
Trowa
Posts
5
Registration date
Monday October 29, 2012
Status
Member
Last seen
November 13, 2012

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
Posts
2805
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 14, 2021
481
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
Posts
5
Registration date
Monday October 29, 2012
Status
Member
Last seen
November 13, 2012

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