Материал из VB Эксперт
Версия от 15:48, 26 мая 2010; Admin (обсуждение | вклад) (1 версия)
(разн.) ← Предыдущая | Текущая версия (разн.) | Следующая → (разн.)
Перейти к: навигация, поиск

Consider only cells that contain data in the form of numbers, text, and formulas

     Sub GetRealLastCell()
         Dim lRealLastRow As Long
         Dim lRealLastColumn As Long
         On Error Resume Next
         lRealLastRow = Cells.Find("*", Range("A1"), xlFormulas,, xlByRows,      xlPrevious).Row
         lRealLastColumn = Cells.Find("*", Range("A1"), xlFormulas,,                 xlByColumns, xlPrevious).Column
         Cells(lRealLastRow, lRealLastColumn).Select
     End Sub

Continuing a Search with the FindNext Method and FindPrevious Method

Sub find()
End Sub

Finding the Last Used Cell in a Column or Row

Function GetLastCellInColumn(rg As Range) As Range 
    Dim lMaxRows As Long 
    lMaxRows = ThisWorkbook.Worksheets(1).Rows.Count 
    If IsEmpty(rg.Parent.Cells(lMaxRows, rg.Column)) Then 
        Set GetLastCellInColumn = _ 
            rg.Parent.Cells(lMaxRows, rg.Column).End(xlUp) 
        Set GetLastCellInColumn = rg.Parent.Cells(lMaxRows, rg.Column) 
    End If 
End Function

Find the First Non-Zero-Length Cell in a Range

Sub FirstNonZeroLength(Rng As Range)
    Dim myCell As Range
    myCell = Range("A1:E3")
    FirstNonZeroLength = 0#
    For Each myCell In Rng
        If Not IsNull(myCell) And myCell <> "" Then
            Debug.Print myCell.Value
            Exit Function
        End If
    Next myCell
    Debug.Print myCell.Value
End Function

Ride the Range from the Bottom to Find Last Row

Sub count()
    cells(Rows.count, 1).End(xlUp).Select
End Sub

Searching for and Replacing Formatting

Sub font()
    With Application.FindFormat.Font
        .Name = "Arial"
        .Size = "12"
        .Bold = True
    End With
    With Application.ReplaceFormat.Font
        .Name = "Arial Black"
        .Bold = False
    End With
    Cells.Replace What:="5", Replacement:="5", LookAt:=xlPart, SearchOrder _
        :=xlByColumns, MatchCase:=False, SearchFormat:=True, ReplaceFormat:=True
End Sub

Uses the Find method to reduce the number of cycles spent in VBA loops:

     Sub DeleteRows2()
         Dim rngFoundCell As range
         Application.ScreenUpdating = False
         Set rngFoundCell = range("C:C").Find(What:="Mangoes")
         Do Until rngFoundCell Is Nothing
             Set rngFoundCell = range("C:C").FindNext
     End Sub

Working with Find and Replace

Sub cell()
    Cells.Find(What:="2008", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        True, SearchFormat:=False).Activate
End Sub