Cách tối ưu code thêm dòng (1 người xem)

Liên hệ QC

Người dùng đang xem chủ đề này

PhanTuHuong

VBA & VB.NET for Excel & AutoCad
Thành viên danh dự
Tham gia
13/6/06
Bài viết
7,210
Được thích
24,721
Tôi có viết đoạn code lập trình sự kiện. Yêu cầu từ ô B24 trở xuống theo hàng dọc, cứ có nội dung là tự động xuống 1 dòng, dòng đó mang đầy đủ nội dung trên. Nghe thì đơn giản nhưng thực hiện khá lằng nhằng (dễ bị lỗi và treo máy), tôi đã làm nhưng chưa được ưng ý vì code khá lằng nhằng.

Hocphi.jpg


Nhờ mọi người tư vấn, tối ưu code:
Mã:
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim BaseCell As Range, BaseRow As Integer
    
    On Error GoTo Thoat
    
    Set BaseCell = Range("B24:B20000")
    
    If Not Intersect(BaseCell, Target) Is Nothing And IsEmpty(Target) = False And IsEmpty(Target.Offset(0, -1)) = False Then
        Rows(Target.Row).Copy
        Rows(Target.Row + 1).Select
        Selection.Insert Shift:=xlDown
        Target.Offset(1, 0).ClearContents
        Application.CutCopyMode = False
        Target.Offset(1, 0).Select
        Set Target = Range("A1")
        Set BaseCell = Nothing
        Exit Sub
    End If
Thoat:
Set BaseCell = Nothing
End Sub
 
Tại sao không dùng List nhỉ? Cần gì viết code!
 
Upvote 0
Tối ưu thuật toán hay tối ưu code thì không dám bàn, nhưng có vài dòng code có vẻ thừa như:
- Rows(Target.Row + 1).Select: có thể insert copy cell bằng cách khác
- Target.Offset(1, 0).Select: nếu nhập liệu xong enter, là đã select cell kế dưới rồi nên dư, còn nếu người dùng quen sử dụng tab thay cho enter để nhập liệu tiếp theo dòng, thì mất công select lại lên trên. Theo tôi làm thế này gọn hơn 1 tí ti.

PHP:
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim BaseCell As Range, BaseRow As Integer
    On Error GoTo Thoat
    Set BaseCell = Range("B14:B200")
    If Not Intersect(BaseCell, Target) Is Nothing And IsEmpty(Target) = False And _ 
    IsEmpty(Target.Offset(0, -1)) = False Then
        Rows(Target.Row).Copy
        Target.Offset(1, 0).EntireRow.Insert Shift:=xlDown
        Target.Offset(1, 0).ClearContents
        Application.CutCopyMode = False
        Set Target = Range("A1")
        Set BaseCell = Nothing
        Exit Sub
    End If
Thoat:
Set BaseCell = Nothing
End Sub

Ghi chú:
1. Nếu dòng 25 là dòng cộng và có công thức Sum, thì tôi luôn để cách dữ liệu 1 dòng trống, khi insert row thì đứng tại dòng trống đó mà insert thì công thức Sum tự cập nhật. Nếu không có dòng trống này, Sum bị thiếu, càng insert càng thiếu.
2. Do copy nguyên dòng và insert copy cells, nên số thứ tự cũng tự copy xuống theo, các cột có công thức cũng vậy, dù người dùng không có ý định nhập liệu vào dòng mới insert đó (vì hết rồi chẳng hạn)
3. Nếu cần insert dòng với đầy đủ công thức như thế, tôi sẽ dùng chức năng List của Excel 2003 hoặc Table của Excel 2007 còn hay hơn, vì ngoại trừ việc thêm dòng, nó còn có thể cancel việc thêm dòng nếu không nhập liệu vào dòng mới thêm.

File kèm theo có 2 sheet, 1 sheet xài code và 1 sheet xài List
 

File đính kèm

Upvote 0
Do thói quen lập trình, mà món List em lại không thạo. Bác cho ví dụ được không?
Thầy quét chọn nguyên vùng dử liệu, Click phải chuột, chọn Create List là xong
Ah, quên, hình như thầy dùng Excel 2007? Cái này hơi khác: Quét chọn vùng dử liệu, vào tab Insert, chọn Table
Sau khi đã tạo List hoặc Table xong, thầy gõ gì đó vào dòng kế cuối là lập tức nó sẽ chèn thêm 1 dòng cho thầy ---> Các công thức ở dòng trên cũng tự động copy xuống dòng dưới
Cái này rất thuận tiện cho việc nhập liệu có dòng tổng cuối trang ---> Chức năng sẳn có, khỏi cần viết gì cả ---> Khỏe re!
 
Upvote 0
Em dùng Office 2007, thực hiện như anh ko được.
 
Upvote 0
Em dùng Office 2007, thực hiện như anh ko được.

Dùng Office2003 thực hiện theo hướng dẫn rất đơn giản Hướng à.

Thầy quét chọn nguyên vùng dử liệu, Click phải chuột, chọn Create List là xong
Ah, quên, hình như thầy dùng Excel 2007? Cái này hơi khác: Quét chọn vùng dử liệu, vào tab Insert, chọn Table
Sau khi đã tạo List hoặc Table xong, thầy gõ gì đó vào dòng kế cuối là lập tức nó sẽ chèn thêm 1 dòng cho thầy ---> Các công thức ở dòng trên cũng tự động copy xuống dòng dưới
Cái này rất thuận tiện cho việc nhập liệu có dòng tổng cuối trang ---> Chức năng sẳn có, khỏi cần viết gì cả ---> Khỏe re!

Nhưng có môt vài vấn đề:

1/ Xuất hiện từ column1, column2.... (có bao nhiêu cột là thêm bấy nhiêu chữ column) phá hỏng định dạng các cột.
2/ Ở cột Số TT, mình để công thức [=IF(B14<>"",A14+1,"")] nó copy xuống dưới nhưng báo lỗi #VALUE!
3/ Có dòng kẻ ngang nhưng khi nhảy dòng xong lại không có

Bạn hướng dẫn giúp mình với nhé

Xin cảm ơn bạn nhiều
 
Upvote 0
Em dùng Office 2007, thực hiện như anh ko được.
Tôi nghĩ Excel 2003 làm được thì lý nào 2007 lại không làm được
Tiếc là tôi không có Excel 2007 để thí nghiệm, tuy nhiên thầy có thể xem cái này:
http://www.jkp-ads.com/articles/Excel2007tables.asp
Trong đó có 1 đoạn:
4. Automatic expansion of table

If you type anything next to a table, Excel assumes you want to expand the table and automatically increases the table size to include your new entry. Of course you can undo this expansion too, or switch off this behavior entirely
1/ Xuất hiện từ column1, column2.... (có bao nhiêu cột là thêm bấy nhiêu chữ column) phá hỏng định dạng các cột.
Hỏng có à nha! Nếu bạn dử liệu đã có tiêu đề rồi thì sau khi tạo Table làm gì có mấy chử Column ấy chứ ---> E rằng bạn đã thao tác sai
2/ Ở cột Số TT, mình để công thức [=IF(B14<>"",A14+1,"")] nó copy xuống dưới nhưng báo lỗi #VALUE!
3/ Có dòng kẻ ngang nhưng khi nhảy dòng xong lại không có
Về công thức thì tôi không biết... Còn vụ Format, trong Excel 2007 có rất nhiều kiểu Format có sằn, còn đẹp hơn rất nhiều so với bạn tự làm
Nghiên cứu xem ---> Tôi không có Excel 2007 nên không thể hướng dẩn cụ thể được, chỉ biết rằng nó còn mạnh hơn Excel 2003 rất nhiều (cái này chắc phải nhờ BNTT hướng dẩn chi tiết quá)
Ngày trước, khi còn xài Excel 2000, để tạo dòng tổng cuối trang người ta phải viết code chứ bây giờ thì không cần ---> Excel 2007 mà không dùng đến Table thì quả là điều đáng tiếc
 
Lần chỉnh sửa cuối:
Upvote 0
Bạn kiểm tra file đính kèm và hướng dẫn giúp mình với nhé

Xin cảm ơn
 

File đính kèm

Upvote 0
Cái vụ Column1, column2 xuất hiện là do hoặc không tô chọn cả tiêu đề, hoặc không đánh dấu vào ô chọn "My table have header" khi create list (hoặc insert table),

attachment.php


Excel 2007 cũng insert dòng như List của 2003, lúc nãy nhầm. Xem file kèm theo và thử gõ dòng liền dưới của table, cột không có công thức.
 

File đính kèm

  • table01.jpg
    table01.jpg
    90.8 KB · Đọc: 363
  • InsertRow.rar
    InsertRow.rar
    13.7 KB · Đọc: 132
Lần chỉnh sửa cuối:
Upvote 0
Thầy thử xem "củ khoai" này có được không ?
 

File đính kèm

Upvote 0
1. List 2003 và Table 2007 có 1 số điểm chung:
- Có dòng Total row, ẩn hiện tùy ý. Có thể chọn công thức total row là sum, Max, Min, Average, ...
- Có thể chèn dòng và copy công thức dòng trên xuống
- Có thể thêm cột vào bên phải bằng cách đơn giản là gõ tiêu đề vào cột kế bên.
- Có 1 hình tam giác nhỏ ở góc dưới bên phải của List - Table để resize
- ....

2. Một số điểm khác nhau:
- Khi hiện dòng Total Row, List 2003 vẫn có thể chèn 1 dòng data & copy công thứcbằng cách gõ dữ liệu vào dòng có dấu * ở trong List, Table thì không. Table chỉ có thể gõ dữ liệu chèn dòng copy công thức khi ẩn total row.
- Table 2007 có thể copy định dạng xuống dòng mới chèn, list 2003 thì có cell copy có cell không.
- ...

3. Một số điểm khác biệt hẳn của Table:
- Table 2007 có nhiều style định dạng.
- Tham chiếu vào trong table trong cùng dòng sẽ có dạng như sau: =Table5[[#This Row];[stien]]. Không biết để làm gì, vì không có [previous row] hoặc [next row]
- Table có thể dùng như 1 Database table trong SQL
- ...

Chắc phải chờ BNTT thật!
 
Upvote 0
Code của bác rất ngắn gọn, nhưng luôn có 1 dòng phía dưới bị ẩn

Vì vậy mới gọi là "củ khoai" chẹp...chẹp... Dòng ẩn ở phía dưới nhằm mục đích giữ định dạng dòng kẻ và giữ phạm vi vùng Sum khi Insert row và xét thấy không ảnh hưởng gì tới bảng tính (nếu dùng List thì dòng cuối cũng luôn có *).

Tôi đã sửa lại theo đúng yêu cầu của bài (không có dòng ẩn) tuy nhiên code phải dài hơn 1 tý...
 

File đính kèm

Lần chỉnh sửa cuối:
Upvote 0
Code của TrungChinh tuy ngắn nhưng lại thừa:

- Đằng nào cũng copy và paste, mà sao chỉ paste format và vẫn phải có những câu lệnh gõ công thức cho từng ô? Nếu 8 ô (nằm trong 8 cột) có công thức thì phải làm 8 dòng sao? Tại sao không copy và Paste All?

- Thêm 1 dòng lệnh tạo công thức Sum, nếu 3, 4 ô Sum nằm trên những cột cách nhau thì phải làm 3, 4 dòng lệnh? Trong khi chỉ cần có 1 dòng trắng xen giữa dòng tổng và dòng dữ liệu cuối cùng, thì Excel tự cập nhật Sum range?

Xem lại thì thấy Code của Hướng tốt hơn nếu chèn trước 1 dòng trắng vào trên dòng tổng. Dòng này cũng có thể dấu đi như file kèm theo.

Code đó dài hơn vì có những dòng khai báo biến, dòng bẫy lỗi, dòng xóa bộ nhớ, bài bản hơn.
 

File đính kèm

Lần chỉnh sửa cuối:
Upvote 0
@ ptm0412 sáng nay em đã sửa lại code khắc phục một số nhược điểm trên rồi bác ạ ! Tuy nhiên tính bài bản thì vẫn chưa. Bác xem lại và góp ý thêm cho em nhé.
Cảm ơn bác nhiều !
Trung Chính
 
Upvote 0
Bạn xem lại bài tôi viết bên trên: "ngắn nhưng thừa"
File mới của bạn sau khi sửa cũng vẫn vậy. Nếu có 8 cột chứa công thức bạn vẫn phải viết 8 dòng lệnh gán công thức; nếu có 4 cột sum bạn sẽ phải dùng 4 câu lệnh gán công thức sum. Mà vẫn phải dùng 3 câu lệnh định dạng border

Tất cả những cái đó chỉ cần dùng 1 câu copy và paste All. Bạn chưa xem file có code của Hướng, hay là bạn chưa thử?

Bỏ các vụ bẫy lỗi, khai báo, xóa bộ nhớ, chỉ có 2 dòng lệnh thực thi chính:
PHP:
Target.EntireRow.Copy
        Target.Offset(1, 0).EntireRow.Insert Shift:=xlDown

Vừa paste All vừa insert tương tự như lệnh insert copy cell trong menu chuột phải.
 
Upvote 0
Thầy quét chọn nguyên vùng dử liệu, Click phải chuột, chọn Create List là xong
Ah, quên, hình như thầy dùng Excel 2007? Cái này hơi khác: Quét chọn vùng dử liệu, vào tab Insert, chọn Table
Sau khi đã tạo List hoặc Table xong, thầy gõ gì đó vào dòng kế cuối là lập tức nó sẽ chèn thêm 1 dòng cho thầy ---> Các công thức ở dòng trên cũng tự động copy xuống dòng dưới
Cái này rất thuận tiện cho việc nhập liệu có dòng tổng cuối trang ---> Chức năng sẳn có, khỏi cần viết gì cả ---> Khỏe re!

Trong Excel 2003, dùng Create List thì được. Nhưng trong 2007, lại là Create Table, nhìn chung trông rất giống nhau (khác mỗi 2 chữ trên) nhưng chức năng thêm dòng lại không thực hiện được.
 
Upvote 0
Trong Excel 2003, dùng Create List thì được. Nhưng trong 2007, lại là Create Table, nhìn chung trông rất giống nhau (khác mỗi 2 chữ trên) nhưng chức năng thêm dòng lại không thực hiện được.

Thầy nói sao ấy chứ... Bây giờ tôi đang thí nghiệm trên Excel 2007 đây, thêm dòng bình thường

attachment.php
 

File đính kèm

  • untitled.JPG
    untitled.JPG
    28.2 KB · Đọc: 272
Upvote 0
NDU thử thêm dòng Total Row vào xem sẽ biết sự khác nhau giữa List và Table.
Đúng rồi, đây là sự khác biệt của 2003 và 2007 ---> Với Excel 2007, muốn tự động Insert dòng thì đừng Total Row ---> Sau khi xong việc ta Total vẫn không muộn
Mình thiết nghĩ mấy cái đơn giản này, mò mò 1 hồi là biết tuốt
Hic..
 
Upvote 0
Đúng rồi, đây là sự khác biệt của 2003 và 2007 ---> Với Excel 2007, muốn tự động Insert dòng thì đừng Total Row ---> Sau khi xong việc ta Total vẫn không muộn
Mình thiết nghĩ mấy cái đơn giản này, mò mò 1 hồi là biết tuốt
Hic..
Cũng không nhất thiết phải bỏ Total Row, thực ra Table của 2007 support khác với List của 2003, thay vì tự động insert dòng trắng thì ở 2007 ta dùng phím tab giống như trong Table của Word, đứng ở ô cuối cùng của dòng dữ liệu cuối nhấn phím Tab là OK. Nhưng theo như yêu cầu của bác Hướng thì nó không đáp ứng được mà chỉ có List của 2003 là đáp ứng đúng mà thôi.
 
Upvote 0
Đúng rồi, đây là sự khác biệt của 2003 và 2007 ---> Với Excel 2007, muốn tự động Insert dòng thì đừng Total Row ---> Sau khi xong việc ta Total vẫn không muộn
Mình thiết nghĩ mấy cái đơn giản này, mò mò 1 hồi là biết tuốt
Hic..
Tại anh hỏng thèm đọc bài của em mà thôi. Nó ở đây nè: http://www.giaiphapexcel.com/forum/showthread.php?t=11863
Còn nếu không thích đọc song ngữ như thế, thì em thấy trong topic này, lão chết tiệt đã nói khá rõ ràng mà...
 
Lần chỉnh sửa cuối:
Upvote 0
Còn nếu không thích đọc song ngữ như thế, thì em thấy trong topic này, lão chết tiệt đã nói khá rõ ràng mà...
Có viết, có file kèm theo, nhưng không đầy đủ bằng nguyên 1 topic của BNTT.
Bây giờ mò mò thêm (ndu's word), mới thấy:
- Khi nhập liệu từ trái qua phải đến hết dòng, mà dùng phím tab, thì khi tab ô cuối, sẽ insert dòng và copy công thức, dù cho có dòng total hiển thị hay không. Ghi chú là insert dòng, nghĩa là đẩy dữ liệu bên dưới table xuống, chứ không phải nối dòng vào table
- Khi dùng chuột điều chỉnh tăng kích thước table bằng cái nút tam giác ở góc dưới bên phải, table tự động giãn dòng và điền công thức vào các dòng mới nối thêm. Ghi chú là nối thêm dòng vào table, nghĩa là nối luôn dữ liệu bên dưới table vào table, nếu nhằm cột có công thức thì chức năng copy công thức cột đó bị mất.
- Nếu gõ dữ liệu vào 1 trong các ô liền kề table, table tự động nới rộng ra 1 cột hoặc 1 dòng, không chèn, không đẩy dữ liệu ngoài table xuống.
- Trong 1 cột trống của table, gõ công thức tính toán cho1 ô (bất cứ ô nào), lập tức cả cột sẽ có công thức y chang.
- Và nhiều cái nữa.
 
Upvote 0
Code chèn thêm dòng!

Chào cả nhà GPE!
Em nhờ anh chị GPE viết dùm em đoạn code để khi nhập liệu thì dòng tự thêm dòng (câu hỏi đã có trong file đính kèm). Thanks
 

File đính kèm

Upvote 0
Chào cả nhà GPE!
Em nhờ anh chị GPE viết dùm em đoạn code để khi nhập liệu thì dòng tự thêm dòng (câu hỏi đã có trong file đính kèm). Thanks
------
Nếu được bạn có thể cho biết dùng để làm gì không? ( tôi thấy lạ quá )
 
Upvote 0
Bạn chọn dòng tiêu đề và dòng trắng đầu --> Click phải --> Create List --> Tick vào My list has headers.
Mỗi lần bạn chọn vào list này và nhập dữ liệu vào dòng có đánh dấu * thì dòng Cộng sẽ tự động bị đẩy xuống. Xem file đính kèm.
 

File đính kèm

Lần chỉnh sửa cuối:
Upvote 0
Bạn tìm với từ khóa List sẽ thấy cách chèn dòng và copy công thức rất hay, không code kiết gì cả. Làm mẫu 1 cái cho bạn xem.
 

File đính kèm

Upvote 0
Chào cả nhà GPE!
Em nhờ anh chị GPE viết dùm em đoạn code để khi nhập liệu thì dòng tự thêm dòng (câu hỏi đã có trong file đính kèm). Thanks
Copy Macro này vào worksheet.
PHP:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect([B:B], Target) Is Nothing And Target.Cells.Count = 1 Then
If Target.Value <> "" Then Target.Offset(1).EntireRow.Insert
End If
End Sub
 
Upvote 0
Copy Macro này vào worksheet.
PHP:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect([B:B], Target) Is Nothing And Target.Cells.Count = 1 Then
If Target.Value <> "" Then Target.Offset(1).EntireRow.Insert
End If
End Sub

Anh ơi, nếu nhập DL vào thì chèn thêm dòng như code anh gửi. Nhưng giả thiết nếu em xóa DL đó đi thì em muốn dòng đó mất đí (hay tự xóa)? Anh vui lòng thiết kế cho em thêm đoạn macro nhé!? Thanks
 
Upvote 0
Anh ơi, nếu nhập DL vào thì chèn thêm dòng như code anh gửi. Nhưng giả thiết nếu em xóa DL đó đi thì em muốn dòng đó mất đí (hay tự xóa)? Anh vui lòng thiết kế cho em thêm đoạn macro nhé!? Thanks
Nếu bạn muốn xóa dòng thì cứ việc xóa dòng. Việc gì phải thông qua Macro. Yêu cầu không hợp lý và không có tính ứng dụng.
 
Upvote 0
dùng cái list không thích hợp ở một số trường hợp trong một sheet có nhiều bảng kèm theo, nó mắc cái head thế là toi mạng. cái list chỉ sài cho bảng đơn giản bảng đơn thoai. có cái nào khác không các pác ơi. thấy mình cũng cần đây
 
Upvote 0

Bài viết mới nhất

Back
Top Bottom