Prev: Removing Command Button
Next: Trying to Modify Excel code; Pointing to SQL Server Instead of Acc
From: asol on 5 May 2010 15:12
hi all

i have in raw A phone list in raw B we have customer that ask to be removed
from call list
what can i do to have clean list in raw C
From: ryguy7272 on 5 May 2010 17:24
If you delete those records, they are gone forever. Maybe you just want to
hide them. Put the word 'Hide' in Column C, in the rows of clients that are
not supposed to be called, and run this:
Sub HideRows()
Dim b As Boolean

Set r = ActiveSheet.UsedRange
nLastRow = r.Rows.Count + r.Row - 1

For rr = 1 To nLastRow
b = False
For cl = 3 To Columns.Count
If Cells(rr, cl).Text = "Hide" Then
b = True
End If
Next
If b Then
Cells(rr, 1).EntireRow.Hidden = True
End If
Next
End Sub



--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"asol" wrote:

> hi all
>
> i have in raw A phone list in raw B we have customer that ask to be removed
> from call list
> what can i do to have clean list in raw C
From: Rich Locus on 5 May 2010 21:23
Hello:

Here is the code to do your copying. If a phone number is found in column
B, it is not copied to Column C:

Option Explicit

Public Sub LastRow()
Dim intLastRowColumnA As Long
Dim intLastRowColumnB As Long
Dim intColumnAPtr As Long
Dim intColumnBPtr As Long
Dim intColumnCPtr As Long
Dim MatchFound As Boolean

intColumnCPtr = 0

' **********************************
' Locate Last Row for Column A and B
' **********************************
intLastRowColumnA = Cells(Rows.Count, "A").End(xlUp).Row
intLastRowColumnB = Cells(Rows.Count, "B").End(xlUp).Row

' *****************************************************
' If A Phone Number Has A Match, Don't Copy To Column C
' *****************************************************
For intColumnAPtr = 1 To intLastRowColumnA
MatchFound = False
For intColumnBPtr = 1 To intLastRowColumnB
If Cells(intColumnAPtr, 1).Value = Cells(intColumnBPtr, 2) Then
MatchFound = True
Exit For
End If
Next intColumnBPtr
If Not MatchFound Then
intColumnCPtr = intColumnCPtr + 1
Cells(intColumnCPtr, 3).Value = Cells(intColumnAPtr, 1).Value
End If

Next intColumnAPtr

End Sub

--
Rich Locus
Logicwurks, LLC


"asol" wrote:

> hi all
>
> i have in raw A phone list in raw B we have customer that ask to be removed
> from call list
> what can i do to have clean list in raw C
From: Rich Locus on 5 May 2010 21:39
Hello:
I think I misunderstood your question in my last post. This version will
see if there is a name in Column B, and if there is a name, it won't copy the
phone number to Column C.

Option Explicit

Public Sub LastRow()
Dim intLastRowColumnA As Long
Dim intColumnAPtr As Long
Dim intColumnCPtr As Long

intColumnCPtr = 0

' **********************************
' Locate Last Row for Column A
' **********************************
intLastRowColumnA = Cells(Rows.Count, "A").End(xlUp).Row

' ********************************************************
' If Column B Has A Name, Don't Copy The Phone Number to C
' ********************************************************
For intColumnAPtr = 1 To intLastRowColumnA
If Cells(intColumnAPtr, 2).Value = "" Then
intColumnCPtr = intColumnCPtr + 1
Cells(intColumnCPtr, 3).Value = Cells(intColumnAPtr, 1).Value
End If
Next intColumnAPtr

End Sub

--
Rich Locus
Logicwurks, LLC


"asol" wrote:

> hi all
>
> i have in raw A phone list in raw B we have customer that ask to be removed
> from call list
> what can i do to have clean list in raw C
From: JLGWhiz on 5 May 2010 21:55
The OP did not define the type of data that is in Col A or Col B. It is
difficult to tell if Col A consists of names and numbers or just name or
just numbers. It is inferred that Col B consists of names but it could be a
number or it could be both. In neither case does it define what data is
located where within the cell, nor how it is delimited if there is a
combination of names and numbers. So, IMHO, it is futile to offer any code
for accomplishing the task.


"Rich Locus" <RichLocus(a)discussions.microsoft.com> wrote in message
news:94ED6C4B-D8B9-49A9-8BC3-D6EF7BD6DB4E(a)microsoft.com...
> Hello:
> I think I misunderstood your question in my last post. This version will
> see if there is a name in Column B, and if there is a name, it won't copy
> the
> phone number to Column C.
>
> Option Explicit
>
> Public Sub LastRow()
> Dim intLastRowColumnA As Long
> Dim intColumnAPtr As Long
> Dim intColumnCPtr As Long
>
> intColumnCPtr = 0
>
> ' **********************************
> ' Locate Last Row for Column A
> ' **********************************
> intLastRowColumnA = Cells(Rows.Count, "A").End(xlUp).Row
>
> ' ********************************************************
> ' If Column B Has A Name, Don't Copy The Phone Number to C
> ' ********************************************************
> For intColumnAPtr = 1 To intLastRowColumnA
> If Cells(intColumnAPtr, 2).Value = "" Then
> intColumnCPtr = intColumnCPtr + 1
> Cells(intColumnCPtr, 3).Value = Cells(intColumnAPtr, 1).Value
> End If
> Next intColumnAPtr
>
> End Sub
>
> --
> Rich Locus
> Logicwurks, LLC
>
>
> "asol" wrote:
>
>> hi all
>>
>> i have in raw A phone list in raw B we have customer that ask to be
>> removed
>> from call list
>> what can i do to have clean list in raw C


 |  Next  |  Last
Pages: 1 2
Prev: Removing Command Button
Next: Trying to Modify Excel code; Pointing to SQL Server Instead of Acc