Excel VBA – delete all blank rows without any content

This is a function to identify when the row(s) doesn’t have any content in each cell, then delete that entire row(s).  It is a amazing function to clean up your spreadsheets before modifying your data.

Function DeleteBlankRows()
Dim Rw As Long, RwCnt As Long, rng As Range

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

On Error GoTo Exits:

If Selection.Rows.Count > 1 Then
Set rng = Selection
Else
Set rng = Range(Rows(1), Rows(ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row()))
End If
RwCnt = 0
For Rw = rng.Rows.Count To 1 Step -1
If Application.WorksheetFunction.CountA(rng.Rows(Rw).EntireRow) = 0 Then
rng.Rows(Rw).EntireRow.Delete
RwCnt = RwCnt + 1
End If
Next Rw

Exits:
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Function

Advertisements

Excel VBA – delete a single column…

This is a function to delete a specific column based on the Column letter and Row number.

e.g. DeleteSingleColumn(“A”, 1), then Column A will be completely deleted

Function DeleteSingleColumn(ByVal validColumn As String, ByVal StartRow As Long)

Dim i As Long
Dim DelString As String

DelString = validColumn & StartRow

Range(DelString).EntireColumn.Delete

End Function

Excel VBA – check if file or directory exists?

This is a function to verify if the desired file or directory exist, it is also important to get the status before any file operation being performed.

If the file or directory exists, it will return TRUE, otherwise, will return FALSE.

Function FileOrDirExists(ByVal PathName As String) As Boolean
‘Macro Purpose: Function returns TRUE if the specified file
     ‘               or folder exists, false if not.
     ‘PathName     : Supports Windows mapped drives or UNC
     ‘             : Supports Macintosh paths
     ‘File usage   : Provide full file path and extension
     ‘Folder usage : Provide full folder path
     ‘               Accepts with/without trailing “\” (Windows)
     ‘               Accepts with/without trailing “:” (Macintosh)

Dim iTemp As Integer

    ‘Ignore errors to allow for error evaluation
    On Error Resume Next
iTemp = GetAttr(PathName)

  ‘Check if error exists and set response appropriately
Select Case Err.Number
Case Is = 0
FileOrDirExists = True
   Case Else
FileOrDirExists = False
  End Select

   ‘Resume error checking
On Error GoTo 0
End Function