Skip to content

Instantly share code, notes, and snippets.

@ndthanh
ndthanh / HEO-tim-dong-cuoi.bas
Last active December 22, 2016 08:40
Học Excel Online - tìm dòng cuối, cột cuối
View HEO-tim-dong-cuoi.bas
lastRow = Range("A" & Sheet1.Rows.Count).End(xlUp).Row
lastcol = Range("A" & Sheet1.Columns.Count).End(xlToLeft).Column
View HEO-tim-dong-cuoi-01.bas
Sheet1.Range("B2").CurrentRegion
Sheet1.Range("C5").CurrentRegion
Sheet1.Range("E9").CurrentRegion
View HEO-tim-dong-cuoi-02.bas
Sub test()
Dim rg As Range, row As Range
Set rg = Sheet1.Range("B2").CurrentRegion
For Each row In rg.Rows
' in ra cửa sổ Immediate Window(Ctrl + G)
Debug.Print row.Cells(1, 1)
Next
End Sub
View gist:f9875789f92fa5a893795910fb157c42
Sub test()
Dim i As Long
For i = 2 To rg.Rows.Count
'in ra cửa sổ Immediate Window(Ctrl + G)
Debug.Print rg.Cells(i, 1)
Next
End Sub
View sql-sample.sql
#lấy toàn bộ các cột báo cáo
SELECT * FROM [A1:U42000]
SELECT [ORDER DATE], [ORDER QUANTITY], [SALES] FROM [A1:U42000]
SELECT [Order ID],[Order Date],[Order Quantity],[Order Priority] ,[Customer Name] FROM [A1:U42000] WHERE [Order Priority] = "High"
SELECT [Order ID],[Order Date],[Order Quantity],[Customer Name] FROM [A1:U42000] WHERE [Order Priority] = "High" AND [Customer Name] = "Annie Cyprus"
SELECT [Order ID],[Order Date],[Order Quantity],[Customer Name],[Ship Mode] FROM [A1:U42000] WHERE [Order Priority] = "High" AND [Ship Mode] LIKE "%Air"
View gist:d51a00a96d816426cab1b750b5400ce4
Sub LastRowInOneColumn()
'Tìm dòng cuối cùng có dữ liệu trong cột A
Dim LastRow As Long
With ActiveSheet
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With
MsgBox LastRow
End Sub
View gist:d9279f9c2f9550b196cf488f8b2fe193
Sub LastColumnInOneRow()
'Find the last used column in a Row: row 1 in this example
Dim LastCol As Integer
With ActiveSheet
LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
End With
MsgBox LastCol
End Sub
View gist:df06a9d61b862866893fc2d1b3bf0916
Sub Range_SpecialCells_Method()
    MsgBox Range("A1").SpecialCells(xlCellTypeLastCell).Address
      
End Sub
View gist:f07ff7d2b6957748c69dd60bd290a199
Sub Range_Find_Method()
'Finds the last non-blank cell on a sheet/range.
Dim lRow As Long
Dim lCol As Long
    
    lRow = Cells.Find(What:="*", _
                    After:=Range("A1"), _
                    LookAt:=xlPart, _
                    LookIn:=xlFormulas, _