อยากทราบวิธีการอัพเดท Name Range โดยใช้ VBA

[mayer02]

ในไฟล์แนบจะมี Name Range อยู่จำนวนหนึ่งที่มีชื่อขึ้นต้นด้วย "W_" สิ่งที่ต้องการคือต้องการอัพเดท Name Range ให้มีค่าปัจจุบันหลังจากมีการอัพเดทข้อมูลเพิ่มเข้าไป เช่น Name Range "W_1st" อ้างถึงเซลล์ Sheet1!$B$2:$B$28 แต่พอเพิ่มข้อมูลเข้าไปเกิน 28 แถว Name Range จะใช้ต่อไม่ได้
ลองทำ Dynamic Name Range แล้วไม่ไหวไฟล์หนักเกินไปครับ
ขอบคุณครับ

[snasui]

กรณี VBA จำเป็นต้องเขียนมาเองก่อน ถามกันเฉพาะที่ติดปัญหาครับ

การเขียน Dynamic named range เดิมเขียนไว้อย่างไรจึงมีปัญหา แจ้งวิธีดังกล่าวมาด้วยเพื่อจะได้ช่วยดูให้ได้ครับ

[mayer02]

ผมเขียนโค๊ดได้ป่ะมาณนี้ครับ

Sub updateName_range()
Dim nr As Name
Dim W_lastrow As Long


W_lastrow = Cells(Rows.Count, "A").End(xlUp).Row

For Each nr In ActiveWorkbook.Names
If nr.Name Like "W_*" Then
Range(nr.Name).Select
With nr

.RefersToR1C1 = "=Sheet1!R2C12:R39C12"

End With
End If
Next nr
End Sub

อยากให้ตรง .RefersToR1C1 มันขยาย Range เองตามข้อมูลแถวสุดท้ายของไฟล์ครับ

[snasui]

แนบ Code มาในไฟล์ด้วยเพื่อสะดวกในการทดสอบครับ

สำหรับการโพสต์ Code ให้อ่านกฎการใช้บอร์ดข้อ 5 ด้านบนประกอบครับ

[mayer02]

แนบไฟล์ให้ใหม่แล้วครับ
ทบทวนปัญหาที่เกิดขึ้นนะครับ
1. ไฟล์ตั้งต้นมีข้อมูล 28 แถว และแต่ละคอลัมน์ มีการสร้าง Name Range ไว้ที่ 28 แถวเช่นกัน
2. มีการอัพเดทข้อมูลมาเป็น 39 แถว

ต้องการสร้าง VBA ให้อัพเดท Name Range จากเดิมที่จบอยู่ที่แถวที่ 28 เป็นแถวที่ 39 (แถวสุดท้ายของข้อมูล) โดยโค๊ดที่สร้างมาข้างต้นดังนี้

Code: Select all

Sub updateName_range()
Dim nr As Name
Dim W_lastrow As Long
	W_lastrow = Cells(Rows.Count, "A").End(xlUp).Row

	For Each nr In ActiveWorkbook.Names
		If nr.Name Like "W_*" Then
		Range(nr.Name).Select
			With nr
			.RefersToR1C1 = "=Sheet1!R2C12:R39C12"
			End With
		End If
	Next nr
End Sub

โค๊ดนี้ติดปัญหาที่ .RefersToR1C1 ยังไม่สามารถอัพเดท Range ได้อัตโนมัติ

[snasui]

ตัวอย่างการปรับ Code ครับ

Code: Select all

Sub updateName_range()
    Dim nr As Name, W_lastrow As Long
    Dim oldFr As Long
    W_lastrow = Cells(Rows.Count, "A").End(xlUp).Row
    For Each nr In ActiveWorkbook.Names
        If nr.Name Like "W_*" Then
            oldFr = Range(nr).Cells(1).Row
            Range(nr).Resize(W_lastrow - oldFr + 1, 1).Name = nr.Name
        End If
    Next nr
End Sub

[mayer02]

work great ขอบคุณครับ