Remove Blank Cells - Single Column - Xlshiftup

     

  • Enables the user to remove blank cells

    • Private Sub CommandButton1_Click()
    Dim max As Integer
    max = Range("A" & Rows.Count).End(xlUp).Row
    Range("A1:A" & max).SpecialCells(xlCellTypeBlanks).Select
    Selection.Delete shift:=xlShiftUp
    End Sub

     

     

     

     

    Remove Blank Cells - Single Column - SpecialCells

     

    Private Sub CommandButton1_Click()
    Columns(1).Select
    Columns(1).SpecialCells(xlCellTypeBlanks).Select
    Selection.Delete Shift:=xlUp
    End Sub

     

     

     

    Remove Blank Cells - Predefined range cells

     

    Private Sub CommandButton1_Click()
    Dim target As Range
    Dim last As Integer
    last = Range("A" & Rows.Count).End(xlUp).Row
    Set target = Range(Range("A1"), Range("A" & last))
    target.SpecialCells(xlCellTypeBlanks).Select
    Selection.Delete shift:=xlUp
    End Sub

     

    Select Non-Blank Cells

     

    Private Sub CommandButton1_Click()
    'select non blank cells
    UsedRange.SpecialCells(xlCellTypeConstants).Select
    With Selection
    .Font.ColorIndex = 5
    .Font.Size = 16
    .Font.Italic = True
    .Font.Bold = True
    .Columns.AutoFit
    End With
    End Sub

     

     

    Select Non-Blank Cells - -Special Cells Method

     

    Private Sub CommandButton1_Click()
    'select non blank cells
    UsedRange.SpecialCells(xlCellTypeConstants).Select
    End Sub

     

     

     

    Copy inclusive of Special Cells - From 1 sheet to another

     

    Private Sub CommandButton1_Click()
    Sheets("Sheet2").Range("A1:A25").SpecialCells(xlCellTypeVisible).Copy Sheets("Sheet3").Range("C6")
    End Sub

     

     

    Highlight Blank Cells

     

  • This program enables to highlight blank cells

    •  

    Private Sub CommandButton1_Click()
    Dim j As Range
    Set j = Range("C3:M16")
    For i = 1 To j.Cells.Count
    If j.Cells(i).Value = "" Then
    j.Cells(i).Interior.ColorIndex = 3
    Else
    j.Cells(i).Interior.ColorIndex = none
    End If
    Next
    End Sub

     

     

     

    Private Sub CommandButton2_Click()
    UsedRange.Interior.ColorIndex = none
    End Sub

     

    Highlight Non Blank Cells

     

    Private Sub CommandButton1_Click()
    UsedRange.Select
    j = UsedRange.Cells.Count
    For i = 1 To j
    If UsedRange.Cells(i).Value <> "" Then
    UsedRange.Cells(i).Interior.ColorIndex = 3
    Else
    UsedRange.Cells(i).Interior.ColorIndex = none
    End If
    Next
    End Sub

     

     

     

    'Remove highlight

    Private Sub CommandButton2_Click()
    UsedRange.Interior.ColorIndex = none
    End Sub

     

     

    Highlight required data

     

    Private Sub CommandButton1_Click()
    j = Range("A1:A101").Cells.Count
    For i = 1 To j
    If Cells(i, 1).Value = "Sriguranjani" Then
    Cells(i, 1).Interior.ColorIndex = 8
    Else
    Cells(i, 1).Interior.ColorIndex = none
    End If
    Next
    End Sub

     

     

    'remove highlight

    Private Sub CommandButton2_Click()
    Range("A1:A101").Interior.ColorIndex = none
    End Sub

     

     

    Highlight other data

     

    Private Sub CommandButton1_Click()
    j = UsedRange.Cells.Count
    For i = 1 To j
    If Cells(i, 1).Value = "Sriguranjani" Then
    Cells(i, 1).Interior.ColorIndex = 0
    Else Cells(i, 1).Interior.ColorIndex = 8
    End If
    Next
    End Sub

     

     

     

    Color alternate Rows

     

  • Through this we can color alternate rows

    •  

    Private Sub CommandButton1_Click()
    Dim j As Range
    Set j = Range("A1:A100")
    For i = 1 To j.Rows.Count
    If i Mod 2 = 1 Then
    j.Rows(i).Interior.ColorIndex = 5
    End If
    Next
    End Sub

     

     

    Color Based on cell value

     

    Private Sub CommandButton1_Click()
    Dim j As Integer
    j = Range(Range("B2"), Range("B2").End(xlDown)).Rows.Count
    For i = 2 To j + 1
    If Cells(i, 2).Value > 35 Then
    Cells(i, 2).Interior.ColorIndex = 5
    Else
    Cells(i, 2).Interior.ColorIndex = 3
    End If
    Next
    End Sub

     

     

     

Hi Everyone,
Welcome to VBA Macros Tutorial. This Tutorial provides comprehensive knowledge on VBA Macros to the user. Hope it enhances your coding skills. If you like this web page kindly Share this to your near and dear. I Prepared this content as per my level of knowledge, if any changes required kindly bring to my notice. Thanks for visiting this web page

Thanks,
Pavan Kumar Gundlapalli