Difference makes the DIFFERENCE

gallery/tryjesus_bl_nobg

Consulting - Quality

Categorised Menu List

Conditionally deletes rows and copy rows into new sheet

A command button cmdDataWork is placed on Ms. Excel User form for ease of execution.

Context: 

We have a huge list of customer data where we have to find out tax id and vendor id and vendor date.

 

How it works: 

  1. To copy the row, where the vendor id is empty into a new work sheet and
  2. delete entire tow where tax id is empty.

DELETE AND COPY DATA

CONDITIONALLY INTO A NEW SHEET

Sub prcSelectData()

Dim i As Long, j As Long
Dim sht As Worksheet

    For Each sht In Sheets
        If sht.Name = "MailData" Then
            blHasSheet = True
            Sheets("MailData").Activate
            Range("A:AZ").Clear
            Exit For
        Else
            blHasSheet = False
        End If
    Next


Set sht = Nothing

    If blHasSheet = False Then
        Sheets.Add
        ActiveSheet.Name = "MailData"
    End If

 

Sheets("Main").Range("A1").EntireRow.Copy
ActiveSheet.Range("A1").PasteSpecial xlPasteAll

Application.ScreenUpdating = False

Sheets("Main").Activate
Range("A2").Select
ActiveCell.End(xlDown).Select
lngRowCount = ActiveCell.Row
ActiveCell.End(xlUp).Select

 

j = 2

For i = 2 To lngRowCount
Application.StatusBar = "Reading data for TaxID is NULL and VENDOR inactive date is NULL..." & j
    
    If Range("B" & i).Value Like "*INACTIVE*" Then Range("B" & i).Delete
    
    If Range("H" & i) = "" And Range("I" & i) = "" Then
        Range("A" & i).EntireRow.Copy
        Sheets("MailData").Range("A" & j).PasteSpecial xlPasteValues
        Application.StatusBar = "Copying data.........." & j
        j = j + 1
    End If
Next

 

Application.StatusBar = "Completed items " & j

Sheets("MailData").Activate
Application.CutCopyMode = False

Issues you may encounter: While you copy and paste the code from here to code window, replace double quotes in code window again, as these quotes will appear as smart quotes.

 

Code file in .txt format

It is always advisable to make best use of status bar.  Application.StatusBar = "some text"

This will help end user to know that the work is in progress...