Clear content and borders of cells in a range

Hello

A macro to adapt

Const range = "A2:G22" Const column = "G" Sub ok() Dim rangeRef As Range, m, obj As Object Dim startCode As Long, endCol As Long, startRow As Long, endRow As Long m = Application.WorksheetFunction.Max(Columns(column)) Set obj = Columns(column).Find(m, , , xlWhole) startRow = obj.Row endRow = Range(Split(range, ":")(1)).Row startCode = Range(Split(range, ":")(0)).Column endCol = Range(Split(range, ":")(1)).Column Set rangeRef = Range(Cells(startRow, startCode), Cells(endRow, endCol)) rangeRef.Value = "" rangeRef.Borders.LineStyle = xlNone End Sub

Best regards

Hello

Thank you for your response.

When I try to run the macro, I get the following message: "Compilation error: End Sub expected"

The macro is as follows:

Const range = "M37:AJ147"
Const coco = "AJ"

Sub ok()
Dim paef As Range, m, obj As Object
Dim codeb As Long, cofin As Long, lideb As Long, lifin As Long
m = Application.WorksheetFunction.Max(Columns(coco))
Set obj = Columns(coco).Find(m, , , xlWhole)
lideb = obj.Row
lifin = Range(Split(range, ":")(1)).Row
codeb = Range(Split(range, ":")(0)).Column
cofin = Range(Split(range, ":")(1)).Column
Set paef = Range(Cells(lideb, codeb), Cells(lifin, cofin))
paef.Value = ""
paef.Borders.LineStyle = xlNone
End Sub

How can I resolve this problem?

When I try to run the macro, the following message appears: "Compilation error: End Sub expected"
Weird, for me, I still copied your code into a module, I was able to compile and run it without error.
Don't you have other macros in your module?
Otherwise, send your file via cjoint.com

Best regards

Hello CCM81

I'm attaching the file. I'm using Excel 2000.
Clicking on Tools Macros brings up a list of 3 macros ("Deuxnuminsertion", "Numéros", "OK"), and clicking on Visual Basic Editor allows you to see the modules, two of which contain macros (Module 6: "Deuxnuminsertion"; Module 5: "Numéros").
I have 3 macros in the macro list and 2 in the modules. Is the problem coming from there?

Best regards

http://www.cjoint.com/c/EKvopxAkGIY

You delete these lines, which declare a procedure that has neither a body (the actual code) nor the end (end sub)

Sub Deuxnuminsertion()
'
' Deuxnuminsertion Macro
' Macro recorded on 11/21/2015 by Roland
'
'

Note: You have some empty modules that you can delete

Best regards

Hello CCM 81

I made the changes presented in the attached file. The message "Runtime Error 91 - Object variable or With block variable not set" appears.
The debugger highlights the line "libed = object.Row" in yellow.

I must have done something somewhere that blocks the macro.

Best regards

http://www.cjoint.com/c/EKxksb7HEgY

In the variable declaration, change the type of the variable m as follows
Dim paef As Range, m As Date, obj As Object

Cdlmnt

Hello CCM81

The macro works, but there is a problem outlined in the attached file.

Best regards

http://www.cjoint.com/c/EKywbVeRHiY

1. As for the color, you add the underlined line

paef.Borders.LineStyle = xlNone
paef.Interior.ColorIndex = 2
End Sub

2. Regarding the error, I believe it comes from the fact that in AI160 you have a value that has nothing to do with the dates at the beginning of the column.
Try this

Const Plage = "M37:AI147"
Const coco = "AI1:AI150"

Sub ok()
Dim paef As Range, m As Date, obj As Object
Dim codeb As Long, cofin As Long, lideb As Long, lifin As Long
m = Application.WorksheetFunction.Max(Range(codate))
Set obj = Range(codate).Find(m, , , xlWhole)
lideb = obj.Row
lifin = Range(Split(Plage, ":")(1)).Row
codeb = Range(Split(Plage, ":")(0)).Column
cofin = Range(Split(Plage, ":")(1)).Column
Set paef = Range(Cells(lideb, codeb), Cells(lifin, cofin))
paef.Value = ""
paef.Borders.LineStyle = xlNone
paef.Interior.ColorIndex = 2
End Sub

Best regards

Hello ccm81

For the deletion you requested, I always received error messages when data was filled starting from row 837. I ended up moving the range M162:AI2004 to leave that range empty. The macro works that way (why?).

The macro correctly changes the background color of the colored cells to white.

Best regards

There was a bug here (a premature paste!)

Const Range = "M37:AI147"
Const coco = "AI1:AI150"

Sub ok()
Dim paef As Range, m As Date, obj As Object
Dim codeb As Long, cofin As Long, lideb As Long, lifin As Long
m = Application.WorksheetFunction.Max(Range(coco))
Set obj = Range(coco).Find(m, , , xlWhole)
lideb = obj.Row
lifin = Range(Split(Range, ":")(1)).Row
codeb = Range(Split(Range, ":")(0)).Column
cofin = Range(Split(Range, ":")(1)).Column
Set paef = Range(Cells(lideb, codeb), Cells(lifin, cofin))
paef.Value = ""
paef.Borders.LineStyle = xlNone
paef.Interior.ColorIndex = 2
End Sub

Best regards

Hello ccm81

The error message 1004 appears: "Unable to read the property of the Worksheet.class.function"

Best regards

You have correctly defined the range coco?
Const coco = "AI1:AI150"