Tweet

Rows, Columns - practical examples Excel VBA

List of practical examples

• Number of rows, columns
• The first empty row
• The last full row
• Find the first empty column
• The last full column
• Finding the last full cells
• Marking rows with the required condition
• Marking column with the required condition
• Deleting empty rows

Number of rows, columns

The total number of rows in a workbook

NumbersRows = Rows.Count MsgBox NumbersRows

The total number of columns in a workbook

NumbersColumns = Columns.Count MsgBox NumbersColumns

The first empty row

Using the cycle:

For RowsNmb = 1 To 65536 If Cells(RowsNmb, 1) = "" Then 'find columns A FirstEmptyRow = RowsNmb Exit For End If Next RowsNmb MsgBox "The first empty row: " & FirstEmptyRow End Sub

Better VBA code without cycle:

LastFullRow = Range("A1").End(xlDown).Row ' find columns A FirstEmptyRow = LastFullRow + 1 MsgBox "The first empty row: " & FirstEmptyRow

Is filled the first two rows? If not (check):

If Range("A1") = "" Then FirstEmptyRow = 1 MsgBox FirstEmptyRow ElseIf Range("A2") = "" Then FirstEmptyRow = 2 MsgBox FirstEmptyRow Else FirstEmptyRow = Range("A1").End(xlDown).Row + 1 MsgBox FirstEmptyRow End If

The last full row

We count from the end.

LastFullRow = Cells(Rows.Count, "B").End(xlUp).Row ' find columns B MsgBox "Last full row have Number: " & LastFullRow

Note: Check the last cell in the row.

Find the first empty column

FirstEmptyColumn = Range("A1").End(xlToRight).Column ' row 1 FirstEmptyColumn = FirstEmptyColumn + 1 MsgBox "First empty column is: " & FirstEmptyColumn

The last full column

LastFullColumn = Cells(2, Columns.Count).End(xlToLeft).Column ' row 2 MsgBox "The last full column is: " & LastFullColumn

Finding the last full cells

Dim PosledniSloupec As Integer Dim PosledniRadek As Long If WorksheetFunction.CountA(Cells) > 0 Then PosledniRadek = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row PosledniSloupec = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column MsgBox ("The last full cells row is: " & PosledniRadek & " column is: " & PosledniSloupec) Else MsgBox ("Blank sheet") End If

Marking rows with the required condition

Dim PosledniRadek As Long, i As Long Dim OznacOblast As Range With Sheets("Sheet1") PosledniRadek = .Range("A" & .rows.Count).End(xlUp).Row For i = 1 To PosledniRadek If Len(Trim(.Range("A" & i).Value)) <> 0 Then If OznacOblast Is Nothing Then Set OznacOblast = .rows(i) Else Set OznacOblast = Union(OznacOblast, .rows(i)) End If End If Next If Not OznacOblast Is Nothing Then ' This area can be copied ... 'OznacOblast.Copy Sheets("List2").Rows(1) OznacOblast.Select End If End With

Marking column with the required condition

In preparation.

Deleting empty rows

Blank lines, is deleted.

' area for deletion ' For i = 30 To 27 Step -1 If StrComp("", Cells(i, "A").Value) = 0 Then MsgBox i Rows(i).Delete End If Next i

Finish

Additional codes in preparation

Článek byl aktualizován: 19.09.2020 10:56

Pavel Lasák - autor webu

Microsoft Office (Word, Excel, Google tabulky, PowerPoint) se věnuji od roku 2000 (od 2004 na této doméně) - V roce 2017 jsem od Microsoft získal prestižní ocenění MVP (zatím jsem jediný z ČR v kategorií Excel). Své vědomosti a zkušenosti dávám k dispozici i on-line ve videích pro SEDUO. Ve firmách školím a konzultuji, učím na MUNI. Tento web již tvořím přes 15 let. Zdarma je zde přes 1.000 návodu, tipů a triků, včetně přes 250 různých šablon, sešitů.

           

Komentáře zatím nejsou

Můžete být prvními co zanechají smysluplný komentář.