Your technology certification is waiting. Enroll in Cloud Class ®
This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.
Dim xCell As Range
Dim xInputSheet As Worksheet
Dim xOutputSheet As Worksheet
Dim xLastRow As Long
Dim i As Long
Dim xName As String
Dim xCompany As String
Dim xDivision As String
Dim xEmail As String
Dim xPhone As String
Dim xTitle As String
Set xInputSheet = ActiveSheet
Set xOutputSheet = Sheets.Add
xOutputSheet.Range("A1:F1").Value = Array("Name", "Company", "Division", "Email Address", "Phone", "Title")
i = 2
xLastRow = ActiveSheet.Range("A1").SpecialCells(xlLastCell).Row
For Each xCell In xInputSheet.Range("A1:A" & xLastRow)
If xCell.Offset(0, 1) <> "" Then
xName = xCell
xTitle = xCell.Offset(0, 1)
If xCell = "" Then
' Ignore blank rows
ElseIf InStr(1, xCell, "@") > 0 Then
xEmail = xCell
ElseIf Left(xCell, 1) = "(" Then
xPhone = xCell
ElseIf IsDate(xCell) Then
' Ignore Date
ElseIf xCell.Offset(-1, 1) <> "" Then
xCompany = xCell
xDivision = xCell
If xCell.Offset(1, 1) <> "" Or xCell.Row = xLastRow Then
xOutputSheet.Cells(i, 1) = xName
xOutputSheet.Cells(i, 2) = xCompany
xOutputSheet.Cells(i, 3) = xDivision
xOutputSheet.Cells(i, 4) = xEmail
xOutputSheet.Cells(i, 5) = xPhone
xOutputSheet.Cells(i, 6) = xTitle
xName = ""
xCompany = ""
xDivision = ""
xEmail = ""
xPhone = ""
xTitle = ""
i = i + 1
Open in new window
ElseIf xCompany = "" Then
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
Please enter a first name
Please enter a last name
Must be at least 4 characters long.
Join and Comment
From novice to tech pro — start learning today.
Premium members can enroll in this course at no extra cost.