Dùng VBA để đánh số thứ tự sau mỗi lần xóa bớt đi những dòng dữ liệu trong bảng tính (1 người xem)

[vungoc]

Nhờ các bạn giúp mình:

Trong bảng tính excel (đính kèm), nếu khi ta xóa đi một dòng bất kỳ nào đó (Xóa nguyên cả dòng - EntireRow.Delete), thì Code trong VBA sẽ tự động đánh lại số thứ tự. Kể từ dòng A2 trở đi, mỗi dòng kế tiếp nhau tăng lên 1 đơn vị cho nguyên bảng dữ liệu này.

(Vui lòng xem file đính kèm)

Mong sớm nhận được sự trợ giúp - Cảm ơn các bạn !

 

[anhtuan1066]

Chỉ sợ Insert Row thì khó chứ còn xóa bớt đâu có vấn đề gì chứ... Chỉ cần công thức cũng đũ rồi

 

[vungoc]

Đây là một câu hỏi em trích xuất trong cái chương trình QUẢN LÝ PHỤ TÙNG em đang xây dựng dở dang.

Em không muốn dùng công thức, chỉ muốn dùng VBA thôi (có lý do phụ thuộc của chương trình này).

Thân ái !

 

[Mr Okebab]

Nhờ các bạn giúp mình:

Trong bảng tính excel (đính kèm), nếu khi ta xóa đi một dòng bất kỳ nào đó (Xóa nguyên cả dòng - EntireRow.Delete), thì Code trong VBA sẽ tự động đánh lại số thứ tự. Kể từ dòng A2 trở đi, mỗi dòng kế tiếp nhau tăng lên 1 đơn vị cho nguyên bảng dữ liệu này.

(Vui lòng xem file đính kèm)

Mong sớm nhận được sự trợ giúp - Cảm ơn các bạn !

Nhưng sao lại bắt buộc xóa nguyên cả dòng vậy bác, sao không xóa bắt đầu từ cột B thôi ??? Như vật STT vẫn giữ nguyên.

File của bác em gần xong rồi, hy vọng ngày mai CF được

Thân!

 

[vungoc]

Xóa từ cột B thì trong List (nhập tạm) sẽ:
Ví dụ: - Lúc đầu có 10 dòng dữ liệu (số thứ tự từ 1 đến 10)
- Ghi tạm xong, thấy sai, xóa đi 2 dòng. Trong trường hợp này nếu xóa từ cột B trở đi thì cột A (STT) vẫn giữ nguyên số TT từ 1 đến số 10, mà thực tế thì dữ liệu chỉ có 8 dòng thôi (vì đã xóa bớt đi 2 dòng rồi).

Cho nên cột số tự chỉ còn lại số từ 1 đến 8 mới đúng (tương ứng với số dòng dữ liệu hiện hành).

Rất mong ngày mai gặp lại bác Hiếu - CF xong nhậu khánh thành file đó luôn !

Thân ái !

 

[Mr Okebab]

Xóa từ cột B thì trong List (nhập tạm) sẽ:
Ví dụ: - Lúc đầu có 10 dòng dữ liệu (số thứ tự từ 1 đến 10)
- Ghi tạm xong, thấy sai, xóa đi 2 dòng. Trong trường hợp này nếu xóa từ cột B trở đi thì cột A (STT) vẫn giữ nguyên số TT từ 1 đến số 10, mà thực tế thì dữ liệu chỉ có 8 dòng thôi (vì đã xóa bớt đi 2 dòng rồi).

Cho nên cột số tự chỉ còn lại số từ 1 đến 8 mới đúng (tương ứng với số dòng dữ liệu hiện hành).

Rất mong ngày mai gặp lại bác Hiếu - CF xong nhậu khánh thành file đó luôn !

Thân ái !

Cái List đó là động, và không lấy theo cột A mà lấy theo cột B bác ạ. Có nghĩa là cột B thay đổi thì nó thay đổi theo, còn STT ở cột A vẫn giữ nguyên.
bác có cần em làm VD không ???

Thân!

 

[SA_DQ]

/-(ình như Vũ /(/gọc phức tạp hóa sự việc hay sao í?!

Option Explicit[b]

Sub NumRowAfterDelete()[/b]
On Error GoTo CFC
 Dim StrC As String, LastRow As Long
 Dim Ij As Long
 Application.ScreenUpdating = False
 LastRow = Range("A65432").End(xlUp).Row
 For Ij = 2 To LastRow
    Range("A" & Ij) = Ij - 1
 Next Ij
 Exit Sub
CFC:  MsgBox "DI NHAU THOI!", , "CFC!" [b]
End Sub[/b]

Bạn cứ thử code này xem ; nếu chạy đúng í thì gán cho nó tổ hợp fím;
Sau các lần xóa dòng thì cho nó chạy lại!

 

[voda]

Bạn thử đoạn code này, cho biết kết quả nhé:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
Dim i, j
i = Cells(Rows.Count, 1).End(xlUp).Row
For j = 1 To i
If Not Intersect(Range("A:A"), Target) Is Nothing Then
Application.EnableEvents = False
Cells(j, 1) = j
End If
Next
Application.EnableEvents = True
End Sub

 

[Mr Okebab]

Option Explicit[B]

Sub NumRowAfterDelete()[/B]
On Error GoTo CFC
 Dim StrC As String, LastRow As Long
 Dim Ij As Long
 Application.ScreenUpdating = False
 LastRow = Range("A65432").End(xlUp).Row
 For Ij = 2 To LastRow
    Range("A" & Ij) = Ij - 1
 Next Ij
 Exit Sub
CFC:  MsgBox "DI NHAU THOI!", , "CFC!" [B]
End Sub[/B]

Bạn cứ thử code này xem ; nếu chạy đúng í thì gán cho nó tổ hợp fím;
Sau các lần xóa dòng thì cho nó chạy lại!

Thực ra cả bác và Vũ Ngọc đều phức tạp hóa vấn đề đấy, cả bác Voda nữa. Vấn đề đơn giản mà, mình chỉ xóa đi từ cột B thôi. Đặt name lại là xong mà. Không cần nhờ đến VBA đâu.

Thân!

 

[vungoc]

Em xin cảm ơn 2 bác SA_DQ & voda.
Cảm ơn luôn bác Okebab.

Chúc các bác luôn mạnh khỏe & trí tuệ
(Đàn em được nhờ dài dài).

Thân mến !

 

[Duong gia]

Bạn thử đoạn code này, cho biết kết quả nhé:

Bạn voda cho hỏi: nếu tôi muốn đánh STT từ dòng số 10 trở xuống thì làm sao?

 

[voda]

Muốn đánh STT bắt đầu từ dòng số 10, bạn chỉnh lại code như sau:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
Dim i, j
i = Cells(Rows.Count, 1).End(xlUp).Row
For j = 10 To i
If Not Intersect(Range("A:A"), Target) Is Nothing Then
Application.EnableEvents = False
Cells(j, 1) = j - 9
End If
Next
Application.EnableEvents = True
End Sub

 

[Duong gia]

Muốn đánh STT bắt đầu từ dòng số 10, bạn chỉnh lại code như sau:

Voda hay thật. Bạn Voda nếu mình muốn nó điền STT chỉ khi B 0 và cách dòng được không? VD cụ thể : B10 -> B13 0 nên cột STT A10 -> A13 là 1, 2, 3 và B150, nó STT tiếp theo là 4

 

[voda]

Nguyên văn bởi Duong gia
Bạn Voda nếu mình muốn nó điền STT chỉ khi B <> 0 và cách dòng được không?

Vấn đề bạn nêu mình thấy trên diễn đàn này, các bạn đã bàn nhiều. Tùy theo yêu cầu mà dùng hàm hay dùng VBA. Bạn dùng thử đoạn code sau đây:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
Dim i, j, k
i = Cells(Rows.Count, 2).End(xlUp).Row
k = 1
Application.EnableEvents = False
Range("A:A").ClearContents
For j = 1 To i
If Not Intersect(Range("A:B"), Target) Is Nothing Then
If Cells(j, 2) <> "" Then
Cells(j, 1) = k
k = k + 1
End If
End If
Next
Application.EnableEvents = True
End Sub

 

[vungoc]

Hàm này của Thầy "Vợ Đá" rất hay!
Cảm ơn Thầy!

 

[quylv]

Don gian thoi ban a.Minh gui lai cho ban nhe
Ban danh ham nay vao O STT o o A2 nhe:
=MAX($A$1:A1)+1

 

[ndu96081631]

Don gian thoi ban a.Minh gui lai cho ban nhe
Ban danh ham nay vao O STT o o A2 nhe:
=MAX($A$1:A1)+1

Vậy là bạn không xem kỹ rồi! Trong trường hợp chèn thêm dòng thì công thức làm sao đây?

 

[cadafi]

Vậy ta có thể dùng công thức này, insert dòng, delete dòng thoải mái luôn:

=ROW()-MATCH("STT",A:A,0)

 

[ndu96081631]

Vậy ta có thể dùng công thức này, insert dòng, delete dòng thoải mái luôn:

Ý mình muốn nói là khi Insert dòng thì dòng mới này đâu có công thức? (ta phải fill bằng tay)
Chính vì lẽ đó để đạt được mức độ tự động cao hơn thì chỉ có mổi cách VBA

 

[quan_giaiphapexcel]

Cần gì phải phức tạp, chỉ dùng công thức đơn giản vẫn dùng thoải mái.Tại ôA2 và A3 (ô có số thứ tự là 1 và 2), từ ô A4 trở đi đặt công thức =Max(A2:A3)+1, sau đó kéo xuống cho đến hết thì thôi. Như vậy sau khi xoá lần lượt nguyên 1 dòng thì số thứ tự sẽ được tự động đánh lại. Nếu xoá nhiều dòng (>2 dòng) thì các ô thứ tự từ dòng bị xoá trở về sau bị lỗi, rê chuột kéo lại thì hết.
Nếu thích thì đặt thêm hàm IF cho mã phụ tùng chư nhập (rỗng) cũng đực. Ví dụ: tại ô A4 đặt công thức =IF(B3<>"";Max(A2:A3)+1;""). Thế là xong.

 

[huuhien80]

Lọc theo tên??

Tôi phải làm về Lâm nghiệp, phải theo dõi nhiều loài cây trên 1 thôn, xã, mỗi loài cây có giá thành khác nhau và từng loài lại có thêm 1-2-3 dạng lập địa khác nhau (mỗi loại lập địa tỷ lệ cây trồng của từng loài cây lại khác nhau)
Dùng công thức IF() nó không thể hiện hết được vì chỉ khoảng 10-15 lần là nó max.
Anh em giúp tôi làm sao khi nhập tên loài cây thì đơn giá của nó sẽ tự động hiện ra tương tự hàm if() nhưng áp dụng được cho nhiều loài. Khi nhập lập địa của loài cây đấy thì tương ứng với loài cây đấy nó sẽ ra mật độ tương ứng của loài có tên cùng hàng.
Rất mong sự giúp đỡ.

 

[QuocPhong]

Mình có 1 file dùng để tính điểm tổng hợp cuối học kỳ. Mình đã tạo một Macro để xuất ra Danh sách học sinh giỏi + tiên tiến ra một sheet khác. Khi sắp xếp lại danh sách (theo Danh hiệu) thì số thứ tự bị đảo lộn. Nhờ các bạn chỉnh code lại cho gọn hơn giúp mình và tạo giúp mình code đánh số thứ tự cho ngắn hơn.
Thanks.

 

[ndu96081631]

Mình có 1 file dùng để tính điểm tổng hợp cuối học kỳ. Mình đã tạo một Macro để xuất ra Danh sách học sinh giỏi + tiên tiến ra một sheet khác. Khi sắp xếp lại danh sách (theo Danh hiệu) thì số thứ tự bị đảo lộn. Nhờ các bạn chỉnh code lại cho gọn hơn giúp mình và tạo giúp mình code đánh số thứ tự cho ngắn hơn.
Thanks.

Code bạn dài quá chừng luôn!
1> Bạn dùng Advanced Filter... với yêu cầu trong file, tôi nghĩ dùng AutoFilter sẽ hay hơn (khỏi tốn vùng điều kiện)
2> Bạn kẻ khung bằng code... Tôi nghĩ dùng Conditional Formating sẽ hay hơn
Code rút gọn:

Sub Loc()
  DH_HK1.Range("A8").CurrentRegion.Offset(1).ClearContents
  With HKI.Range(HKI.[A6], HKI.[B65536].End(xlUp)).Resize(, 20)
    .AutoFilter 20, "<>--"
    Union(.Offset(1, 0).Resize(, 4), .Offset(1, 16).Resize(, 4)).SpecialCells(12).Copy
    DH_HK1.Range("A9").PasteSpecial 3
    .Parent.AutoFilterMode = False
  End With
  DH_HK1.Range("B65536").End(xlUp).Offset(1).EntireRow.ClearContents
  With DH_HK1.Range("A8").CurrentRegion
    .Sort .Cells(2, 8), 1, .Cells(2, 5), , 2, , , xlGuess
    If .Rows.Count > 1 Then
      .Resize(, 1).SpecialCells(2, 1).Value = Evaluate("ROW(R1:R1000)")
    End If
  End With
End Sub

Xem file

 

[QuocPhong]

Code bạn dài quá chừng luôn!
1> Bạn dùng Advanced Filter... với yêu cầu trong file, tôi nghĩ dùng AutoFilter sẽ hay hơn (khỏi tốn vùng điều kiện)
2> Bạn kẻ khung bằng code... Tôi nghĩ dùng Conditional Formating sẽ hay hơn

Cảm ơn bạn. Mình không rành VBA nên không biết cách nào viết để cho gọn lại. Record Macro thử nên mới dài như vậy. Hic, code của bạn mình mình xem không hiểu gì hết. Chắc phải để dành ngâm cứu dài dài.

 

[ndu96081631]

Cảm ơn bạn. Mình rành VBA nên không biết cách nào viết để cho gọn lại. Record Macro thử nên mới dài như vậy. Hic, code của bạn mình mình xem không hiểu gì hết. Chắc phải để dành ngâm cứu dài dài.

Thật ra code này cũng giống code bạn thôi!
Giờ bạn thử record macro quá trình lọc bằng AutoFilter xem... Đương nhiên code có được sau khi Record macro sẽ khá dài, bạn từ từ so sánh và tự rút gọn
Chú ý: Mấy cái With... End With chỉ nhầm mục đích viết cho gọn (dể nhìn)... ngoài ra chẳng có mục đích gì khac!
Quy trình lọc như sau:
- AutoFilter dử liệu nhập tại cột thứ 20, với điều kiện "<>--" (lấy những em nào khác dấu -- )
- Copy 4 cột đầu + 4 cột kế cuối qua sheet Loc (dùng Union để gộp 2 vùng không nằm cạnh nhau lại)
- Sau khi lọc xong, chuyện còn lại chỉ là: Sort và đánh số thứ tự

 

[ChanhTQ@]

Chú ý: Mấy cái With... End With chỉ nhầm mục đích viết cho gọn (dể nhìn)... ngoài ra chẳng có mục đích gì khac!

Không hẵn là vậy đâu NDU!

Nó tăng tốc hơn nhiều đó nha!

Nó cũng giống như 1 người nhận 3 lệnh:
(1) Vô buồng tăm đo chiều cao;
(2) Vô buồng tắm đo chiều rộng
(3) Vô buồng tắm đo độ cao bồn cầu

Nếu Ta không dùng With. . . End With

Nó sẽ thực hiện xong 1 lệnh, lại chạy ra nơi đã nghe lệnh đó, rồi sau đó quay lại buồng tắm đó nha!

Còn nếu có With. . End With thì Nó sẽ ở trong buồng tắm, thực hiện xong ba lệnh nó mới ra. Khà, . . khà . . . .

Không tin cứ thử coi!

Tất nhiên, trong With . . . End with mà ra các lệnh khác thì không chắc . . . !

 

[ndu96081631]

Không hẵn là vậy đâu NDU!

Nó tăng tốc hơn nhiều đó nha!

Là em nói đến "mục đích" trong code của em ấy mà sư phụ

 

[MNThao]

Cần gì phải phức tạp, chỉ dùng công thức đơn giản vẫn dùng thoải mái.Tại ôA2 và A3 (ô có số thứ tự là 1 và 2), từ ô A4 trở đi đặt công thức =Max(A2:A3)+1, sau đó kéo xuống cho đến hết thì thôi. Như vậy sau khi xoá lần lượt nguyên 1 dòng thì số thứ tự sẽ được tự động đánh lại. Nếu xoá nhiều dòng (>2 dòng) thì các ô thứ tự từ dòng bị xoá trở về sau bị lỗi, rê chuột kéo lại thì hết.
Nếu thích thì đặt thêm hàm IF cho mã phụ tùng chư nhập (rỗng) cũng đực. Ví dụ: tại ô A4 đặt công thức =IF(B3<>"";Max(A2:A3)+1;""). Thế là xong.

Có một cách khác để giải quyết được vấn đề xóa 2 hay nhiều dòng của bác đấy là thay vì công thức ở A4 như bác vừa nói ở trên thì ta thay bằng công thức =Max(A$2:A3)+1. Việc này cho bác kéo thỏai mái và xóa cũng thỏai mái luôn. Chỉ đừng có xóa mất 2 có hàng 2 và 3 của em là được rồi

 

[tungson_mrcc]

With DH_HK1.Range("A8").CurrentRegion
.Sort .Cells(2, 8), 1, .Cells(2, 5), , 2, , , xlGuess
If .Rows.Count > 1 Then
.Resize(, 1).SpecialCells(2, 1).Value = Evaluate("ROW(R1:R1000)")
End If
End With

Bác NDU ơi, nhờ Bác vui lòng giải thích kỹ đoạn code này với. Tôi mới chỉ hiểu được phần sort. Còn từ đoạn resize trở đi thì chỉ đoán mò mà thôi.
1. Ở phần SpecialCells(2,1), theo VBA help, tôi thấy các giá trị XlCellType và XlSpecialCellsValue, trong khi ở đây Bác lại dùng giá trị số. Vậy mối tương quan giữa chúng với nhau ra sao?
2. Rồi còn phương thức Evaluate nữa chứ, tôi đọc trong VBA help và chẳng hiểu gì cả. Xem thêm bài viết của Bác Duyệt ở đây thì vỡ thêm được dấu ngoặc vuông. Tóm lại là vẫn chưa hiểu được cách sử dụng phương thức Evaluate

Mong Bác quan tâm giúp đỡ.

Trân trọng.

 

[ndu96081631]

Bác NDU ơi, nhờ Bác vui lòng giải thích kỹ đoạn code này với. Tôi mới chỉ hiểu được phần sort. Còn từ đoạn resize trở đi thì chỉ đoán mò mà thôi.
1. Ở phần SpecialCells(2,1), theo VBA help, tôi thấy các giá trị XlCellType và XlSpecialCellsValue, trong khi ở đây Bác lại dùng giá trị số. Vậy mối tương quan giữa chúng với nhau ra sao?
2. Rồi còn phương thức Evaluate nữa chứ, tôi đọc trong VBA help và chẳng hiểu gì cả. Xem thêm bài viết của Bác Duyệt ở đây thì vỡ thêm được dấu ngoặc vuông. Tóm lại là vẫn chưa hiểu được cách sử dụng phương thức Evaluate

Mong Bác quan tâm giúp đỡ.

Trân trọng.

SpecialCells(2,1) Tương đương với việc bạn bấm Ctrl + G\Special, chọn mục Contents và check mục Number (số bao nhiêu là dựa vào việc bạn đếm trong khung Special từ trên xuống xem mục mình chọn nằm vị trí bao nhiêu thôi)
Ví dụ thêm:
SpecialCells(1) Tương đương với việc bạn bấm Ctrl + G\Special, chọn mục Comments
SpecialCells(3) Tương đương với việc bạn bấm Ctrl + G\Special, chọn mục Formulas
--------------------------------------------------------------------------------------
Evaluate chuyên dùng để tính ra giá trị của 1 biểu thức hoặc 1 công thức
Evaluate("ROW(R1:R1000)") tương đương với việc bạn quét chọn cột nào đó rồi gõ vào thanh Formula công thức =ROW(R1:R1000) rồi Ctrl + Shift + Enter ---> sau đó copy và paste value để biến công thức thành giá trị
Ví dụ thêm: Evaluate("5+4-1") sẽ cho kết quả =8 ---> Chính là giá trị của biểu thức "5+4-1" sau khi tính toán