Difference makes the DIFFERENCE
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:
- To copy the row, where the vendor id is empty into a new work sheet and
- 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.
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...