Lấy địa chỉ ô đầu, ô cuối vùng dữ liệu ?

TrungChinhs · 1/5/09

Vùng dữ liệu nằm ở vị trí bất kỳ trong bảng tính (ví dụ B5:H15) tôi sử dụng câu lệnh [A1].SpecialCells(2).CurrentRegion.Select thì chọn được toàn bộ vùng dữ liệu, vậy tôi muốn lấy địa chỉ ô đầu, ô cuối của vùng dữ liệu (Vd B5 và H5) thì code viết như thế nào ? Thanks !

P/s Kết quả tìm được có thể ghi vào môt ô nào đó tuỳ ý ngoài vùng dữ liệu vd: A5 và I15

cadafi · 1/5/09

Trung Chinh đã viết:
Vùng dữ liệu nằm ở vị trí bất kỳ trong bảng tính (ví dụ B5:H15) tôi sử dụng câu lệnh [A1].SpecialCells(2).CurrentRegion.Select thì chọn được toàn bộ vùng dữ liệu, vậy tôi muốn lấy địa chỉ ô đầu, ô cuối của vùng dữ liệu (Vd B5 và H5) thì code viết như thế nào ? Thanks !

Code lấy ô Đầu Tiên:

PHP:

a = [A1].SpecialCells(2).CurrentRegion.Cells(1, 1).Address

Code lấy ô cuối:

PHP:

b = [A1].SpecialCells(xlCellTypeLastCell).Address

Hoặc tổng quát hơn:

PHP:

With [A1].SpecialCells(2).CurrentRegion
c = .Cells(.Rows.Count, .Columns.Count).Address
End With

Tuy nhiên, mình vẫn thấy code lấy ô cuối cùng trong vùng dữ liệu chưa ổn. Mọi người sẽ giúp bạn tiếp nhé!

ChanhTQ@ · 1/5/09

Bạn thử với macro này xem sao

PHP:

Option Explicit
Sub LastRowCells()
 Dim Rng As Range, LastRow As Long
 
 Set Rng = [A1].SpecialCells(2).CurrentRegion
 If WorksheetFunction.CountA(Rng) > 0 Then
   LastRow = Rng.Find(What:="*", After:=Rng.Cells(1, 1), SearchOrder:=xlByRows, _
      SearchDirection:=xlPrevious).Row
   MsgBox LastRow, , Rng.Cells(1, 1).Row
 End If
End Sub

TrungChinhs · 1/5/09

@ ChanhTQ@: Tôi đã test thử code của bạn, kết quả trả về là dòng đầu và dòng cuối của vùng (thiếu cột đầu, cột cuối), bạn sửa lại nội dung thông báo trong Msgbox (dòng dầu nó nhẩy lên trên title)

ChanhTQ@ · 1/5/09

Bạn đến đây & tham khảo để sửa lại theo ý

http://www.giaiphapexcel.com/forum/showthread.php?t=1641#10

ndu96081631 · 2/5/09

Trung Chinh đã viết:
Vùng dữ liệu nằm ở vị trí bất kỳ trong bảng tính (ví dụ B5:H15) tôi sử dụng câu lệnh [A1].SpecialCells(2).CurrentRegion.Select thì chọn được toàn bộ vùng dữ liệu, vậy tôi muốn lấy địa chỉ ô đầu, ô cuối của vùng dữ liệu (Vd B5 và H5) thì code viết như thế nào ? Thanks !

P/s Kết quả tìm được có thể ghi vào môt ô nào đó tuỳ ý ngoài vùng dữ liệu vd: A5 và I15

Anh có thể nói rõ thêm về dự định của anh được không?
Em vẫn cảm giác có gì đó không ổn với đoạn SpecialCells(2).CurrentRegion

TrungChinhs · 2/5/09

ndu96081631 đã viết:
Anh có thể nói rõ thêm về dự định của anh được không?
Em vẫn cảm giác có gì đó không ổn với đoạn SpecialCells(2).CurrentRegion

Trước đây tôi có hỏi code tự động xác định vùng dữ liệu tại bài
http://www.giaiphapexcel.com/forum/showthread.php?t=13623

Sau khi nghiên cứu tôi thấy rằng từ một ô bất kỳ (giả sử tôi chọn A1) muốn đưa con trỏ vào dữ liệu ta dùng lệnh [A1].SpecialCells(2) nhưng do lệnh này chỉ chọn các ô có dữ liệu mà không chọn toàn bộ vùng (bao gồm cả các ô trống trong vùng) do đó phải thực hiện thêm lệnh .CurrentRegion. Sau khi test thử với câu lệnh [A1].SpecialCells(2).CurrentRegion.Selec thì thấy nó đáp ứng được yêu cầu tự động xác định vùng dữ liệu (theo tôi thì là ổn). Nhưng tôi cần biết thêm vùng dữ liệu này nằm ở vị trí nào trong bảng tính (địa chỉ ô đầu, địa chỉ ô cuối) để lấy một địa chỉ nào đó gán cho một câu lệnh khác thay vì phải chỉ định chính xác cột, dòng
ví dụ trong câu lệnh: Rng = Range("C2:C" & [D65536].End(xlUp).Row) thì Rng bắt buộc phải nằm ở cột C nếu Rng được xác định tuỳ biến từ địa chỉ vừa tìm thấy thì Rng có thể nằm ở cột bất kỳ người dùng có thể vận dụng vào trường hợp tương tự mà không cần phải sửa lại code hoặc sửa lại bảng tính

Cụ thể tôi đang viết code tự động tạo mục lục bằng Outline (về cơ bản đã tạm ổn chỉ còn thiếu phần tuỳ biến của vùng dữ lệu. Xem file đính kèm). Rất mong được sự giúp đỡ của các bạn. Thanks!

ndu96081631 · 2/5/09

Với code của anh, nếu là tôi thì tôi viết khác 1 chút:

PHP:

Private Sub Worksheet_Activate()
  Dim Clls As Range, i As Long
  Application.ScreenUpdating = False
  Cells.ClearOutline
  With Range([D2], [D65536].End(xlUp)).SpecialCells(2)
    For i = 1 To .Areas.Count
      .Areas(i)(0, -1) = i: .Areas(i).Rows.Group
    Next
  End With
  Sheet1.Outline.ShowLevels 1
  Application.ScreenUpdating = True
End Sub

- Khỏi cần phải chèn chử "End" vào cell cuối làm điểm kết thúc
- Khỏi công thức
- Dấu chấm sau mấy số thứ tự thì dùng Custom Format mà làm

ChanhTQ@ · 2/5/09

Mình cũng cảm thấy không ổn trong câu lệnh đó, & thử như sau

Bảng thử nghiệm:

A|B|C|D
TT|Ten||Ngay
1|By||5/3/2009
2|Bi||5/4/2009
3|Ba||5/5/2009
4|Be||5/6/2009
5|Bu||5/7/2009
6|Bo||5/8/2009
7|Bý||5/9/2009
8|Bé||5/10/2009

Mà trong đó từ 'D2

9' ta lập công thức =TODAY()+ROW()

Sao khi cho chạy macro dưới đây, &. . .
Vậy bạn cần vùng nào trong 5 địa chỉ này?

PHP:

Option Explicit
Sub LamRo()
 
 MsgBox [A1].SpecialCells(2).Address, , "SpecialCells(2)"
 MsgBox [A1].SpecialCells(2).CurrentRegion.Address, , "SpecialCells(2).CurrentRegion"
 MsgBox [A1].SpecialCells(3).Address, , "SpecialCells(3)"
 MsgBox [A1].SpecialCells(3).CurrentRegion.Address, , "SpecialCells(3).CurrentRegion"
 
 MsgBox ActiveSheet.UsedRange.Address, , "UsedRange"
  
End Sub

TrungChinhs · 2/5/09

Cảm ơn Ndu và ChanhTQ@ !
@ ndu: code của bạn thật là độc đáo (người học "luôn luôn lắng nghe" nhưng "lâu lâu mới hiểu"). Tôi vẫn chưa hiểu câu lệnh .Areas(i)(0, -1) = i ở đây (0, -1) có nghĩa là gì ? (trông thì giống như offset (0, -1)) ?.
Còn cái vụ dùng Custom Format quả thực mình không nghĩ đến (Custom Format thì biết nhưng it khi dùng nó).

@ ChanhTQ@: Câu hỏi của mình đã được giải đáp tại câu thứ 2 trong bài của bạn: MsgBox [A1].SpecialCells(2).CurrentRegion.Address, , "SpecialCells(2).CurrentRegion". Nhưng kết quả từ các câu còn lại lại gợi ý thêm cho mình những ý tưởng mới, mình sẽ tiếp tục nghiên cứu vận dụng.

ndu96081631 · 2/5/09

Trung Chinh đã viết:
Cảm ơn Ndu và ChanhTQ@ !
@ ndu: code của bạn thật là độc đáo (người học "luôn luôn lắng nghe" nhưng "lâu lâu mới hiểu"). Tôi vẫn chưa hiểu câu lệnh .Areas(i)(0, -1) = i ở đây (0, -1) có nghĩa là gì ? (trông thì giống như offset (0, -1)) ?.

Cái này cũng dể lắm
Ví dụ anh đã Set Rng = vùng nào đó thì
Rng(1,1) chính là cell đầu tiên (bên trái, trên cùng)
Từ đó suy ra:
Rng(0,1) là cell nằm trên cell đầu tiên
Suy ra tiếp
Rng(0,0) là cell nằm trên cell đầu tiên và nằm bên trái cell đầu tiên
Vậy Rng(0,0) <==> Rng.Offset(-1,-1).Resize(1,1)
Suy ra tiếp
Rng(0,-1) <==> Rng.Offset(-1,-2).Resize(1,1)
Anh để ý sẽ thấy cách viết Rng(x,y) hơn kém với Offset(x,y) 1 đơn vị
Nói chung, khi viết Rng(x,y) sẽ giống y chang hàm INDEX(Rng, x, y) mà anh dùng trong Excel, tức dòng x, cột y của vùng Rng (và anh cũng thấy INDEX và OFFSET trong công thức Excel hơn kém nhau 1 đơn vị về trị số dòng, cột đấy thôi)
Có điều Rng(x,y) trong VBA còn "ngầu" hơn INDEX rất nhiều... "Ngầu" ở chổ cho phép truy xuất cell nằm ngoài vùng Rng luôn
Nói thêm:
- Nếu anh không thích cách viết trên, có thể sửa lại thành kiểu Vung.Offset(....) ---> Tại em thấy viết kiểu này gọn hơn
- Nếu anh dùng Offset buộc anh phải thêm Resize(1,1) để chắc ăn cell anh cần là 1 cell duy nhất (rõ ràng cách viết sẽ dài hơn)
----------------
Còn Areas ấy à!
Anh thử thế này:
- Chọn cột D
- Bấm Ctrl + G\SpecialCells\Contents
- Anh sẽ thấy vùng chọn được chia làm nhiều phần (là những cell có dử liệu)
- Vậy mỗi vùng sẽ được gọi là 1 Area
- Vung.Areas.Count chính là tổng số vùng anh đếm được
- Vung.Areas(i) chính là vùng thứ i

TrungChinhs · 3/5/09

ndu96081631 đã viết:
...
Nói thêm:
- Nếu anh không thích cách viết trên, có thể sửa lại thành kiểu Vung.Offset(....) ---> Tại em thấy viết kiểu này gọn hơn
- Nếu anh dùng Offset buộc anh phải thêm Resize(1,1) để chắc ăn cell anh cần là 1 cell duy nhất (rõ ràng cách viết sẽ dài hơn)
...

Cảm ơn Ndu: cả buổi chiều hôm qua chờ bài này nhưng không thấy trả lời (tối qua mình có việc bận, không vào mạng) cứ nghĩ rằng bạn không quan tâm nên sáng nay định post lại câu hỏi này sang chủ đề khác.
Mình mới đọc lướt qua bài của bạn, ngắn gọn và dễ hiểu. Qua các bài của bạn mình thấy bạn rất sáng tạo, một phong cách độc đáo, riêng có trên diễn đàn này. Mong rằng bạn sẽ tiếp tục cống hiến cho GPE nhiều bài hay để mọi người cùng học tập.

SA_DQ · 4/5/09

Nếu vùng dữ liệu được chọn không liên tục thì sao?

Vùng dữ liệu nằm ở vị trí bất kỳ trong bảng tính (ví dụ B5:H15) tôi sử dụng câu lệnh [A1].SpecialCells(2).CurrentRegion.Select thì chọn được toàn bộ vùng dữ liệu, vậy tôi muốn lấy địa chỉ ô đầu, ô cuối của vùng dữ liệu (Vd B5 và H5) thì code viết như thế nào ?

Trong ví dụ theo bảng dưới đây, nào đâu là ô đầu & đâu là ô cuối vậy các bạn? (Nếu vùng chọn là các ô chứa dữ liệu & i - chỉ số của dòng; k - chỉ số của cột)

||k|k+1
i-1|||GPE
i||35|
i+1||25|Hồng
i+2||Thơm
. . .

TrungChinhs · 4/5/09

HYen17 đã viết:
Trong ví dụ theo bảng dưới đây, nào đâu là ô đầu & đâu là ô cuối vậy các bạn? (Nếu vùng chọn là các ô chứa dữ liệu & i - chỉ số của dòng; k - chỉ số của cột)

||k|k+1
i-1|||GPE
i||35|
i+1||25|Hồng
i+2||Thơm
. . .

Chào bác HYen, em đang dùng phương pháp CurrentRegion để xác định vùng dữ liệu vì vậy vùng phải là hình chữ nhật, vùng gồm các ô có dữ liệu hoặc không có dữ liệu (miễn là không có dòng rỗng hoặc cột rỗng) vì vậy ô đầu của vùng phải là ô góc trên trái và ô cuối của vùng phải là ô góc dưới phải;
em sử dụng cách của Ca_dafi
a = Sheets(1).[A1].SpecialCells(2).CurrentRegion.Cells(1, 1).Address
b = Sheets(1).[A1].SpecialCells(xlCellTypeLastCell).Address
tìm được địa chỉ ô đầu (a) và địa chỉ ô cuối (b) của vùng, giờ em muốn chọn vùng từ a đến b thì câu lệnh viết như thế nào ? em viết Range(a, b) nhưng không được.
Nhờ Bác và các bạn viết giúp câu lệnh này. Thanks!

ndu96081631 · 4/5/09

Trung Chinh đã viết:
Chào bác HYen, em đang dùng phương pháp CurrentRegion để xác định vùng dữ liệu vì vậy vùng phải là hình chữ nhật, vùng gồm các ô có dữ liệu hoặc không có dữ liệu (miễn là không có dòng rỗng hoặc cột rỗng) vì vậy ô đầu của vùng phải là ô góc trên trái và ô cuối của vùng phải là ô góc dưới phải;
em sử dụng cách của Ca_dafi
a = Sheets(1).[A1].SpecialCells(2).CurrentRegion.Cells(1, 1).Address
b = Sheets(1).[A1].SpecialCells(xlCellTypeLastCell).Address
tìm được địa chỉ ô đầu (a) và địa chỉ ô cuối (b) của vùng, giờ em muốn chọn vùng từ a đến b thì câu lệnh viết như thế nào ? em viết Range(a, b) nhưng không được.
Nhờ Bác và các bạn viết giúp câu lệnh này. Thanks!

Nếu địa chỉ a và b có tồn tại thì đượng nhiên là Range(a,b) sẽ được xác định!
Anh kiểm tra lại a, b trước nhé!
Anh xem thí nghiệm nhé!

PHP:

Sub Test()
 Dim a As String, b As String
 a = Sheets(1).[A1].SpecialCells(2).CurrentRegion.Cells(1, 1).Address
 b = Sheets(1).[A1].SpecialCells(xlCellTypeLastCell).Address
 MsgBox a
 MsgBox b
 Range(a, b).Select
End Sub

SA_DQ · 4/5/09

Khi tìm ô cuối của vùng dữ liệu, ta phải chú í như sau

Find the last Row, Column or Cell

You can use Edit>Go to-Special-Last cell to try and find the last cell in the active sheet, but it is not very reliable. The reasons are two-fold:

1. The last cell is only re-set when you save. This means if you enter any number or text in say, cell A10 and A20 of a new Worksheet, then delete the content of A20, the Edit>Go to-Special-Last cell will keep taking you to A20, until you save.

2. It picks up cell fomatting. Let's say you enter any text or number in cell A10 and then enter a valid date in cell A20 of a new Worksheet. Now delete the date in cell A20 and save. The Edit>Go to-Special-Last cell will still take you to A20. This is because entering a date in A20 has caused Excel to automatically format the cell from "General" to a Date format. To stop from going to A20 you will have to use Edit>Clear>All and then save.

So when using VBA you cannot rely on:
Range("A1").SpecialCells(xlCellTypeLastCell).Select

(http://www.ozgrid.com/VBA/ExcelRanges.htm)

TrungChinhs · 4/5/09

ndu96081631 đã viết:
Nếu địa chỉ a và b có tồn tại thì đượng nhiên là Range(a,b) sẽ được xác định!
Anh kiểm tra lại a, b trước nhé!
Anh xem thí nghiệm nhé!

PHP:

Sub Test() Dim a As String, b As String a = Sheets(1).[A1].SpecialCells(2).CurrentRegion.Cells(1, 1).Address b = Sheets(1).[A1].SpecialCells(xlCellTypeLastCell).Address MsgBox a MsgBox b Range(a, b).Select End Sub

@ Ndu: cảm ơn bạn rất nhiều, từ bài của bạn, mình mới phát hiện ra mình sai do đã khai báo biến a và b là kiểu Range.
Xin phép bạn cho mình sửa code của bạn thành code của "mình" nha và mình rất muốn bạn sửa code của mình thành code của bạn để cho mình học hỏi thêm (ví dụ trong file đính kèm).

PHP:

Sub Sh_Sh_Ndu()
    Dim i As Long
    For i = 3 To Sheets.Count
      Sheets(i).Range("A1:G19").Value = Sheets(2).Range("A1:G19").Value
    Next
  End Sub

PHP:

Sub Sh_Sh_Chinh()
    Dim i As Long, a As String, b As String
    a = Sheets(2).[A1].SpecialCells(2).CurrentRegion.Cells(1, 1).Address
    b = Sheets(2).[A1].SpecialCells(xlCellTypeLastCell).Address
    For i = 3 To Sheets.Count
      Sheets(i).Range(a, b) = Sheets(2).[A1].SpecialCells(2).CurrentRegion.Value
    Next
  End Sub

phamduylong · 4/5/09

Trung Chinh đã viết:
a = Sheets(1).[A1].SpecialCells(2).CurrentRegion.Cells(1, 1).Address
b = Sheets(1).[A1].SpecialCells(xlCellTypeLastCell).Address
tìm được địa chỉ ô đầu (a) và địa chỉ ô cuối (b) của vùng, giờ em muốn chọn vùng từ a đến b thì câu lệnh viết như thế nào ? em viết Range(a, b) nhưng không được.
Nhờ Bác và các bạn viết giúp câu lệnh này. Thanks!

Tôi thì thích dùng cách này:

Mã:

Sub MyCurrentRegion()
rd = Selection.CurrentRegion.Cells(1).Row
cd = Selection.CurrentRegion.Cells(1).Column
rc = rd + Selection.CurrentRegion.Rows.Count - 1
cc = Selection.CurrentRegion.Columns.Count + cd - 1
Range(Cells(rd, cd), Cells(rc, cc)).Select
End Sub

Với :
Selection: ô, vùng đang chọn.
rd,cd: dòng, cột đầu của ô đầu vùng CurrentRegion
rc,cc: dòng, cột của ô cuối vùng CurrentRegion
Xác định được rd, cd, rc, cc thì:
Cells(rd, cd).Select: chọn ô đầu tiên.
Cells(rc, cc).Select: chọn ô cuối cùng.
Range(Cells(rd, cd), Cells(rc, cc)).Select: chọn vùng CurrentRegion

TrungChinhs · 4/5/09

phamduylong đã viết:
Tôi thì thích dùng cách này:

Mã:

Sub MyCurrentRegion() rd = Selection.CurrentRegion.Cells(1).Row cd = Selection.CurrentRegion.Cells(1).Column rc = rd + Selection.CurrentRegion.Rows.Count - 1 cc = Selection.CurrentRegion.Columns.Count + cd - 1 Range(Cells(rd, cd), Cells(rc, cc)).Select End Sub

Với :
Selection: ô, vùng đang chọn.
rd,cd: dòng, cột đầu của ô đầu vùng CurrentRegion
rc,cc: dòng, cột của ô cuối vùng CurrentRegion
Xác định được rd, cd, rc, cc thì:
Cells(rd, cd).Select: chọn ô đầu tiên.
Cells(rc, cc).Select: chọn ô cuối cùng.
Range(Cells(rd, cd), Cells(rc, cc)).Select: chọn vùng CurrentRegion

@ Bác Long em đã test thử code của Bác, với cách này ta phải đặt con trỏ vào vùng dữ liệu trước khi chạy code (nếu con trỏ đặt ngoài vùng dữ liệu thì không được). code trên em đang nghiên cứu là mặc con trỏ nằm ở bất kỳ vị trí nào do đó ta có thể làm việc với một Sheet không mở hoặc đang ẩn.

phamduylong · 4/5/09

Trung Chinh đã viết:
@ Bác Long em đã test thử code của Bác, với cách này ta phải đặt con trỏ vào vùng dữ liệu trước khi chạy code (nếu con trỏ đặt ngoài vùng dữ liệu thì không được). code trên em đang nghiên cứu là mặc con trỏ nằm ở bất kỳ vị trí nào do đó ta có thể làm việc với một Sheet không mở hoặc đang ẩn.

Một sheet có thể có nhiều CurrentRegion. Phải có 1 địa chỉ cụ thể để xác định CurrentRegion. Ví dụ:
Range("A1").CurrentRegion....
Selection.CurrentRegion...
Cells(1,1).CurrentRegion...
Khi viết Range("A1"), Selection, Cells(1,1) VBA xem như đang làm việc với sheet hiện hành (Active). Nếu muốn làm việc với một sheet của một workbook nào đó đang mở, chỉ cần khai báo đầy đủ tên workbook, tên sheet, địa chỉ ô cần tìm CurrentRegion.
Ví dụ, tìm CurrentRegion của ô A1 sheet Data, workbook Book1:
rd = Workbooks("Book1").Sheets("Data").Cells(1, 1).CurrentRegion.Cells(1).Row
cd = Workbooks("Book1").Sheets("Data").Cells(1, 1).CurrentRegion.Cells(1).Column
rc = Workbooks("Book1").Sheets("Data").Cells(1, 1).CurrentRegion.Rows.Count + rd - 1
cc = Workbooks("Book1").Sheets("Data").Cells(1, 1).CurrentRegion.Columns.Count + cd - 1

Lấy địa chỉ ô đầu, ô cuối vùng dữ liệu ?

Thành viên tích cực

Hành động từ trái tim

0901452không62

Thành viên tích cực

0901452không62

Huyền thoại GPE

Thành viên tích cực

File đính kèm

Huyền thoại GPE

File đính kèm

0901452không62

Thành viên tích cực

Huyền thoại GPE

Thành viên tích cực

/(hông là gì!

Thành viên tích cực

Huyền thoại GPE

File đính kèm

/(hông là gì!

Thành viên tích cực

File đính kèm

-

Thành viên tích cực

-