Useing If to Delete a Range
 

HY ALL !
My big problem is the next : I work in a whole
worksheet ,[ from cell 91 to cell 65536 ] ; I have need a
macro which to ClearContents of Range ,,C91:BB91,,
if the value of cell BD91 < = 32 ; then next row , the
same problem : if BD92 < = 32 , then , to delete ,,the Range" :
,,C92:BB92,, ; then , next row , and so on , from Row 91 to Row
65536 . Please very much to help me !


The macro can look like this [ I write this for pattern ] ,
but how can I modify it ?



Sub Macrocomanda3()

Dim Row As Integer, Col As Integer

For Col = 1 To 4
For Row = 1 To 10
If ActiveSheet.Cells(Row, Col) = 1 Then
ActiveSheet.Cells(Row, Col).ClearContents
Next Row
Next Col

End Sub


RESPECTFULLY TO ALL !

Useing If to Delete a Range
 

work from the BOTTOM up. Something like

mc="bd"
for i = cells(rows.count,mc).end(xlup).row to 2 step -1
if cells(i,mc)<=32 then range(cells(i,"c"),cells(i,"bb")).clearcontents
next i

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"ytayta555" wrote in message
...
HY ALL !
My big problem is the next : I work in a whole
worksheet ,[ from cell 91 to cell 65536 ] ; I have need a
macro which to ClearContents of Range ,,C91:BB91,,
if the value of cell BD91 < = 32 ; then next row , the
same problem : if BD92 < = 32 , then , to delete ,,the Range" :
,,C92:BB92,, ; then , next row , and so on , from Row 91 to Row
65536 . Please very much to help me !


The macro can look like this [ I write this for pattern ] ,
but how can I modify it ?



Sub Macrocomanda3()

Dim Row As Integer, Col As Integer

For Col = 1 To 4
For Row = 1 To 10
If ActiveSheet.Cells(Row, Col) = 1 Then
ActiveSheet.Cells(Row, Col).ClearContents
Next Row
Next Col

End Sub


RESPECTFULLY TO ALL !

Useing If to Delete a Range
 

Two problems. You did not have an End If andyou should not use Row which is
a reserved word. Prefixing your variables to indicate the type of variable
gets over the problem of using reserved words.

Try this:-

Sub Macrocomanda3()

Dim intRow As Integer, intCol As Integer

For intCol = 1 To 4
For intRow = 1 To 10
If ActiveSheet.Cells(intRow, intCol) = 1 Then
ActiveSheet.Cells(intRow, intCol).ClearContents
End If
Next intRow
Next intCol

End Sub


--
Regards,

OssieMac


"ytayta555" wrote:

HY ALL !
My big problem is the next : I work in a whole
worksheet ,[ from cell 91 to cell 65536 ] ; I have need a
macro which to ClearContents of Range ,,C91:BB91,,
if the value of cell BD91 < = 32 ; then next row , the
same problem : if BD92 < = 32 , then , to delete ,,the Range" :
,,C92:BB92,, ; then , next row , and so on , from Row 91 to Row
65536 . Please very much to help me !


The macro can look like this [ I write this for pattern ] ,
but how can I modify it ?



Sub Macrocomanda3()

Dim Row As Integer, Col As Integer

For Col = 1 To 4
For Row = 1 To 10
If ActiveSheet.Cells(Row, Col) = 1 Then
ActiveSheet.Cells(Row, Col).ClearContents
Next Row
Next Col

End Sub


RESPECTFULLY TO ALL !

Useing If to Delete a Range
 

Hi again,

You might be better not using integers for the row numbers because integers
only work with numbers in the range -32,768 to 32,767.

Try this instead:-

Sub Macrocomanda3()

Dim lngRow As Long, lngCol As Long

For lngCol = 1 To 4
For lngRow = 1 To 10
If ActiveSheet.Cells(lngRow, lngCol) = 1 Then
ActiveSheet.Cells(lngRow, lngCol).ClearContents
End If
Next lngRow
Next lngCol

End Sub


--
Regards,

OssieMac


"OssieMac" wrote:

Two problems. You did not have an End If andyou should not use Row which is
a reserved word. Prefixing your variables to indicate the type of variable
gets over the problem of using reserved words.

Try this:-

Sub Macrocomanda3()

Dim intRow As Integer, intCol As Integer

For intCol = 1 To 4
For intRow = 1 To 10
If ActiveSheet.Cells(intRow, intCol) = 1 Then
ActiveSheet.Cells(intRow, intCol).ClearContents
End If
Next intRow
Next intCol

End Sub


--
Regards,

OssieMac


"ytayta555" wrote:

HY ALL !
My big problem is the next : I work in a whole
worksheet ,[ from cell 91 to cell 65536 ] ; I have need a
macro which to ClearContents of Range ,,C91:BB91,,
if the value of cell BD91 < = 32 ; then next row , the
same problem : if BD92 < = 32 , then , to delete ,,the Range" :
,,C92:BB92,, ; then , next row , and so on , from Row 91 to Row
65536 . Please very much to help me !


The macro can look like this [ I write this for pattern ] ,
but how can I modify it ?



Sub Macrocomanda3()

Dim Row As Integer, Col As Integer

For Col = 1 To 4
For Row = 1 To 10
If ActiveSheet.Cells(Row, Col) = 1 Then
ActiveSheet.Cells(Row, Col).ClearContents
Next Row
Next Col

End Sub


RESPECTFULLY TO ALL !

Useing If to Delete a Range
 

Thank you very much , now I am trying
to work with your sugestions ; I said that
the macro is for pattern , but my range to delete
[if BD91(X) , < = 32 ], is C919(x):BB91(x);
thank you again for your amability .

DDE

Useing If to Delete a Range
 

Hi Don,

You only need to work backwards from the bottom if deleting rows. With
Clearcontents, it does not matter. I think that the OP's main problem was as
stated "My big problem is the next " because the End If had been omitted.

--
Regards,

OssieMac


"Don Guillett" wrote:

work from the BOTTOM up. Something like

mc="bd"
for i = cells(rows.count,mc).end(xlup).row to 2 step -1
if cells(i,mc)<=32 then range(cells(i,"c"),cells(i,"bb")).clearcontents
next i

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"ytayta555" wrote in message
...
HY ALL !
My big problem is the next : I work in a whole
worksheet ,[ from cell 91 to cell 65536 ] ; I have need a
macro which to ClearContents of Range ,,C91:BB91,,
if the value of cell BD91 < = 32 ; then next row , the
same problem : if BD92 < = 32 , then , to delete ,,the Range" :
,,C92:BB92,, ; then , next row , and so on , from Row 91 to Row
65536 . Please very much to help me !


The macro can look like this [ I write this for pattern ] ,
but how can I modify it ?



Sub Macrocomanda3()

Dim Row As Integer, Col As Integer

For Col = 1 To 4
For Row = 1 To 10
If ActiveSheet.Cells(Row, Col) = 1 Then
ActiveSheet.Cells(Row, Col).ClearContents
Next Row
Next Col

End Sub


RESPECTFULLY TO ALL !

Useing If to Delete a Range
 

On 5 Mar, 03:41, OssieMac wrote:
Hi Don,

You only need to work backwards from the bottom if deleting rows. With
Clearcontents, it does not matter. I think that the OP's main problem was as
stated "My big problem is the next " because the End If had been omitted.

--
Regards,

OssieMac



"Don Guillett" wrote:
work from the BOTTOM up. Something like

mc="bd"
for i = cells(rows.count,mc).end(xlup).row to 2 step -1
if cells(i,mc)<=32 then range(cells(i,"c"),cells(i,"bb")).clearcontents
next i

--
Don Guillett




1= I must declare the Range ,,myRange" C91:BB91 and i don't know how;
2=then I must do something like this : For myRange = C91:BB91 To
C65536:BB65536
3=I must set Column BD {mean56} , in this macro ;
3=I think I must put 1 Next : ,,Next lngRow"
and the macro with this settings can look so ..(I think..) :


Dim lngRow As Long, lngCol As Long

lngCol = "BD"

For lngRow = 91 To 65536
If ActiveSheet.Cells(lngRow, lngCol) )<=32 then
range(cells(myRange)),clearcontents

End If
Next lngRow

End Sub

This macro must understand something like : if BD91 <=32 then
EntireRow.ClearContents...BUT I
have need to clear only range C91:BB91 of the Row of active cell ;
I specificate that the initial macro is for pattern ;
please help me with this new sugestion .

Respectfully to All

Useing If to Delete a Range
 

Hi,
Try this code:

Sub Clear_Ranges()

' Macro recorded 3/5/2008 by Tim

Dim cell As Range, rng As Range
Set rng = Range(Cells(1, "BD"), Cells(Rows.Count, "BD").End(xlUp))
For Each cell In rng
If cell.Value < 32 Then
Cells(cell.Row, "C").Select
Selection.Resize(1, 52).Select
Selection.ClearContents
End If
Next
End Sub

Regards,
Tim




"ytayta555" wrote:

On 5 Mar, 03:41, OssieMac wrote:
Hi Don,

You only need to work backwards from the bottom if deleting rows. With
Clearcontents, it does not matter. I think that the OP's main problem was as
stated "My big problem is the next " because the End If had been omitted.

--
Regards,

OssieMac



"Don Guillett" wrote:
work from the BOTTOM up. Something like

mc="bd"
for i = cells(rows.count,mc).end(xlup).row to 2 step -1
if cells(i,mc)<=32 then range(cells(i,"c"),cells(i,"bb")).clearcontents
next i

--
Don Guillett




1= I must declare the Range ,,myRange" C91:BB91 and i don't know how;
2=then I must do something like this : For myRange = C91:BB91 To
C65536:BB65536
3=I must set Column BD {mean56} , in this macro ;
3=I think I must put 1 Next : ,,Next lngRow"
and the macro with this settings can look so ..(I think..) :


Dim lngRow As Long, lngCol As Long

lngCol = "BD"

For lngRow = 91 To 65536
If ActiveSheet.Cells(lngRow, lngCol) )<=32 then
range(cells(myRange)),clearcontents

End If
Next lngRow

End Sub

This macro must understand something like : if BD91 <=32 then
EntireRow.ClearContents...BUT I
have need to clear only range C91:BB91 of the Row of active cell ;
I specificate that the initial macro is for pattern ;
please help me with this new sugestion .

Respectfully to All

Useing If to Delete a Range
 

Correct, it doesn't matter but I do it out of habit because sometimes clear
and sometimes delete. So, I think OP can use my original code by changing to
2 so that it is now to 91, like this leaving columns A:B and bc alone.
If desired, change to
for i = 91 to cells(rows.count,mc).end(xlup).row


sub clearmycontents()
mc="bd"
for i = cells(rows.count,mc).end(xlup).row to 91 step -1
if cells(i,mc)<=32 then range(cells(i,"c"),cells(i,"bb")).clearcontents
next i
end sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"ytayta555" wrote in message
...
On 5 Mar, 03:41, OssieMac wrote:
Hi Don,

You only need to work backwards from the bottom if deleting rows. With
Clearcontents, it does not matter. I think that the OP's main problem was
as
stated "My big problem is the next " because the End If had been omitted.

--
Regards,

OssieMac



"Don Guillett" wrote:
work from the BOTTOM up. Something like

mc="bd"
for i = cells(rows.count,mc).end(xlup).row to 2 step -1
if cells(i,mc)<=32 then range(cells(i,"c"),cells(i,"bb")).clearcontents
next i

--
Don Guillett




1= I must declare the Range ,,myRange" C91:BB91 and i don't know how;
2=then I must do something like this : For myRange = C91:BB91 To
C65536:BB65536
3=I must set Column BD {mean56} , in this macro ;
3=I think I must put 1 Next : ,,Next lngRow"
and the macro with this settings can look so ..(I think..) :


Dim lngRow As Long, lngCol As Long

lngCol = "BD"

For lngRow = 91 To 65536
If ActiveSheet.Cells(lngRow, lngCol) )<=32 then
range(cells(myRange)),clearcontents

End If
Next lngRow

End Sub

This macro must understand something like : if BD91 <=32 then
EntireRow.ClearContents...BUT I
have need to clear only range C91:BB91 of the Row of active cell ;
I specificate that the initial macro is for pattern ;
please help me with this new sugestion .

Respectfully to All

Useing If to Delete a Range
 

HI ALL

I tried Tim's Macro and it work perfect , that's what
I needed ; excuse me because I wasn't clear , {and
here you must be so if you want to get the right ad-
vice ; }

I've done only 2 changes in Tim's Macro [for my particular need] :

in second line of macro :Set rng = Range(Cells(1, "BD"),
Cells(Rows.Count, "BD").End(xlUp)) ,
I must changed so : Set rng = Range(Cells(91, "BD"),
Cells(Rows.Count, "BD").End(xlUp));
and the 4-th line of macro :If cell.Value < 32 Then
becomed : If cell.Value <= 32 Then

ALL YOUR ADVICE BUILT THIS MACRO !! THIS CASE IS RESOLVED !
Thank you to ALL . YOU ARE ALL WONTHERFULL

{[ps: I'll post soon a new subiect very strong and very hard..I
think... , but very usefull !!! and intersting !!!
I have a solution , but it isn't the complete solution ! Then , I
shall give my solution which I have
for the subiect I'll post soon ; I was looking for this solution
(which I have for my next subiect I'll post)
some months ; I think here shall be the time of teachers ]}

Respectfully again ,
soon

Useing If to Delete a Range
 

Did you also remove the selections?

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"ytayta555" wrote in message
...
HI ALL

I tried Tim's Macro and it work perfect , that's what
I needed ; excuse me because I wasn't clear , {and
here you must be so if you want to get the right ad-
vice ; }

I've done only 2 changes in Tim's Macro [for my particular need] :

in second line of macro :Set rng = Range(Cells(1, "BD"),
Cells(Rows.Count, "BD").End(xlUp)) ,
I must changed so : Set rng = Range(Cells(91, "BD"),
Cells(Rows.Count, "BD").End(xlUp));
and the 4-th line of macro :If cell.Value < 32 Then
becomed : If cell.Value <= 32 Then

ALL YOUR ADVICE BUILT THIS MACRO !! THIS CASE IS RESOLVED !
Thank you to ALL . YOU ARE ALL WONTHERFULL

{[ps: I'll post soon a new subiect very strong and very hard..I
think... , but very usefull !!! and intersting !!!
I have a solution , but it isn't the complete solution ! Then , I
shall give my solution which I have
for the subiect I'll post soon ; I was looking for this solution
(which I have for my next subiect I'll post)
some months ; I think here shall be the time of teachers ]}

Respectfully again ,
soon