i have a single page worksheet with various information that
automaticly changes and sorts based on another worksheet. whith that
said here is my issue

i need a script that checks wether there is information in colum (A).

if there is information in (A) then leave (O) alone. if not then clear
(O)

example if (A7) = blank, clear (O7)

my range is (A2:A5002) and (O2:O5002)

any insight would be great

you are now unstuck:

Sub demo()
Dim r As Range
Set r = Range("A2:A5002")
For Each rr In r
If IsEmpty(rr.Value) Then
rr.Offset(0, 14).Clear
End If
Next
End Sub
--
Gary's Student
gsnu200703


" wrote:

i have a single page worksheet with various information that
automaticly changes and sorts based on another worksheet. whith that
said here is my issue

i need a script that checks wether there is information in colum (A).

if there is information in (A) then leave (O) alone. if not then clear
(O)

example if (A7) = blank, clear (O7)

my range is (A2:A5002) and (O2:O5002)

any insight would be great

This should be close...

Sub ClearO()
Dim rngBlank As Range

On Error Resume Next
Set rngBlank = Range("A2:A5002").SpecialCells(xlCellTypeBlanks)
On Error GoTo 0

If rngBlank Is Nothing Then
MsgBox "nothing to clear"
Else
rngBlank.Offset(0, 14).ClearContents
End If
End Sub
--
HTH...

Jim Thomlinson


" wrote:

i have a single page worksheet with various information that
automaticly changes and sorts based on another worksheet. whith that
said here is my issue

i need a script that checks wether there is information in colum (A).

if there is information in (A) then leave (O) alone. if not then clear
(O)

example if (A7) = blank, clear (O7)

my range is (A2:A5002) and (O2:O5002)

any insight would be great

Try

Sub AAA()
Dim Rng As Range
Dim SaveCalc As XlCalculation
SaveCalc = Application.Calculation
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
Application.ScreenUpdating = False
For Each Rng In Range("A2:A5002").SpecialCells(xlCellTypeConstants )
If Rng.Value = vbNullString Then
Rng.EntireRow.Cells(1, "O").Value = vbNullString ' letter 'O'
not number 0.
End If
Next Rng
Application.Calculation = SaveCalc
Application.ScreenUpdating = False
Application.EnableEvents = True
End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)


wrote in message
oups.com...
i have a single page worksheet with various information that
automaticly changes and sorts based on another worksheet. whith that
said here is my issue

i need a script that checks wether there is information in colum (A).

if there is information in (A) then leave (O) alone. if not then clear
(O)

example if (A7) = blank, clear (O7)

my range is (A2:A5002) and (O2:O5002)

any insight would be great

and for yet another sample:

Option Explicit

Sub mediatenyc()

Dim MyLastRow As Long
Dim MyRange As Range
Dim StartRow As Long
Dim MyRow As Long
Dim i As Long

MyLastRow = Cells(20000, 1).End(xlUp).Row

Set MyRange = Range("a1:o" & MyLastRow)

i = 1

Do While i < MyLastRow
If Range("a" & i) = "" Then
MyRow = i
Range("o" & MyRow).Clear
End If
i = i + 1
Loop

End Sub


On Jan 29, 12:45 pm, "Chip Pearson" wrote:
Try

Sub AAA()
Dim Rng As Range
Dim SaveCalc As XlCalculation
SaveCalc = Application.Calculation
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
Application.ScreenUpdating = False
For Each Rng In Range("A2:A5002").SpecialCells(xlCellTypeConstants )
If Rng.Value = vbNullString Then
Rng.EntireRow.Cells(1, "O").Value = vbNullString ' letter 'O'
not number 0.
End If
Next Rng
Application.Calculation = SaveCalc
Application.ScreenUpdating = False
Application.EnableEvents = True
End Sub

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLCwww.cpearson.com
(email address is on the web site)

wrote in ooglegroups.com...



i have a single page worksheet with various information that
automaticly changes and sorts based on another worksheet. whith that
said here is my issue

i need a script that checks wether there is information in colum (A).

if there is information in (A) then leave (O) alone. if not then clear
(O)

example if (A7) = blank, clear (O7)

my range is (A2:A5002) and (O2:O5002)

any insight would be great- Hide quoted text -- Show quoted text -

If your using a version of excel prior to xl2000 - not likely - but in that
case, the behavior of Offset is different. In those versions you would use:

Sub ClearO()
Dim rngBlank As Range
Dim rng1 as Range
On Error Resume Next
Set rngBlank = Range("A2:A5002").SpecialCells(xlCellTypeBlanks)
On Error GoTo 0

If rngBlank Is Nothing Then
MsgBox "nothing to clear"
Else
set rng1 = Intersect(rngBlank.EntireRow,Range("O:O"))
rng1.ClearContents
End If
End Sub

Just as in Jim's original post, this assumes the cells in column A are
actually empty and don't just appear empty, but are not.

--
Regards,
Tom Ogilvy

"Jim Thomlinson" wrote:

This should be close...

Sub ClearO()
Dim rngBlank As Range

On Error Resume Next
Set rngBlank = Range("A2:A5002").SpecialCells(xlCellTypeBlanks)
On Error GoTo 0

If rngBlank Is Nothing Then
MsgBox "nothing to clear"
Else
rngBlank.Offset(0, 14).ClearContents
End If
End Sub
--
HTH...

Jim Thomlinson


" wrote:

i have a single page worksheet with various information that
automaticly changes and sorts based on another worksheet. whith that
said here is my issue

i need a script that checks wether there is information in colum (A).

if there is information in (A) then leave (O) alone. if not then clear
(O)

example if (A7) = blank, clear (O7)

my range is (A2:A5002) and (O2:O5002)

any insight would be great

so what am i doing wrong, i used colum (O) as an example its really
colum (M) but i changed the 14 to a 12 in
(( rr.Offset(0, 12).Clear))

here is whats in the vbs editor
------------------------------------------------------------------

Private Sub Workbook_Open()

ActiveSheet.Unprotect Password:="1111"

Columns("A:N").Select

Selection.Sort Key1:=Range("K1"), Order1:=xlAscending, Header:=yes,
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

Dim r As Range
Set r = Range("A2:A5002")
For Each rr In r
If IsEmpty(rr.Value) Then
rr.Offset(0, 12).Clear
End If
Next

ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True, Password:="onlyone1"

ActiveWorkbook.Save

End Sub
_________________________________________________

i actually tried all 4 cant figure out what im doing wrong

ahhh well no the cells arent really empty they just appear empty.
sorry there are formulas in every cell

On Jan 29, 1:33 pm, wrote:
so what am i doing wrong, i used colum (O) as an example its really
colum (M) but i changed the 14 to a 12 in
(( rr.Offset(0, 12).Clear))

here is whats in the vbs editor
------------------------------------------------------------------

Private Sub Workbook_Open()

ActiveSheet.Unprotect Password:="1111"

Columns("A:N").Select

Selection.Sort Key1:=Range("K1"), Order1:=xlAscending, Header:=yes,
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

Dim r As Range
Set r = Range("A2:A5002")
For Each rr In r
If IsEmpty(rr.Value) Then
rr.Offset(0, 12).Clear
End If
Next

ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True, Password:="onlyone1"

ActiveWorkbook.Save

End Sub
_________________________________________________

i actually tried all 4 cant figure out what im doing wrong

Sub demo()
Dim r As Range
Set r = Range("A2:A5002")
For Each rr In r
If len(trim(rr.Value)) = 0 Then
rr.Offset(0, 14).Clear
End If
Next
End Sub

--
Regards,
Tom Ogilvy



" wrote:

ahhh well no the cells arent really empty they just appear empty.
sorry there are formulas in every cell

On Jan 29, 1:33 pm, wrote:
so what am i doing wrong, i used colum (O) as an example its really
colum (M) but i changed the 14 to a 12 in
(( rr.Offset(0, 12).Clear))

here is whats in the vbs editor
------------------------------------------------------------------

Private Sub Workbook_Open()

ActiveSheet.Unprotect Password:="1111"

Columns("A:N").Select

Selection.Sort Key1:=Range("K1"), Order1:=xlAscending, Header:=yes,
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

Dim r As Range
Set r = Range("A2:A5002")
For Each rr In r
If IsEmpty(rr.Value) Then
rr.Offset(0, 12).Clear
End If
Next

ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True, Password:="onlyone1"

ActiveWorkbook.Save

End Sub
_________________________________________________

i actually tried all 4 cant figure out what im doing wrong

Thanks Tom, I'll save this one.
--
Gary's Student
gsnu200703


"Tom Ogilvy" wrote:

Sub demo()
Dim r As Range
Set r = Range("A2:A5002")
For Each rr In r
If len(trim(rr.Value)) = 0 Then
rr.Offset(0, 14).Clear
End If
Next
End Sub

--
Regards,
Tom Ogilvy



" wrote:

ahhh well no the cells arent really empty they just appear empty.
sorry there are formulas in every cell

On Jan 29, 1:33 pm, wrote:
so what am i doing wrong, i used colum (O) as an example its really
colum (M) but i changed the 14 to a 12 in
(( rr.Offset(0, 12).Clear))

here is whats in the vbs editor
------------------------------------------------------------------

Private Sub Workbook_Open()

ActiveSheet.Unprotect Password:="1111"

Columns("A:N").Select

Selection.Sort Key1:=Range("K1"), Order1:=xlAscending, Header:=yes,
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

Dim r As Range
Set r = Range("A2:A5002")
For Each rr In r
If IsEmpty(rr.Value) Then
rr.Offset(0, 12).Clear
End If
Next

ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True, Password:="onlyone1"

ActiveWorkbook.Save

End Sub
_________________________________________________

i actually tried all 4 cant figure out what im doing wrong