I have a Excel file named STOCKS24 which contains following sheets: JANUARY, FEBRUARY,... upto DECEMBER, RULES, STATS. Each sheet that has month name consists of a table named with first three letters of corresponding month name. For example sheet named JANUARY has a table named JAN. Likewise for remaining sheets having month as their name. I want a Excel macro that deletes the content of certain cells of these sheets. Those cells contains trade entries for last year and I want to clear those entries for fresh entries of this year. The table of each month name sheet contains following columns: EntryDate, Close Date, SCRIP, LONG/SHORT, EntryPrice, ExitPrice, R1, R2, R3, R4, R5, NTR1, NTR2, RightSL, Continuation. I want to delete the content of these columns except the content of last that is total row. I am also having following merged cells: AC2 + AD2, AC3 + AD3, AC4 + AD4. Where I have used+ sign to tell you that these cells are merged. I want to delete content of these for all month name sheets that is excluding or except for RULES and STATS sheets. There is also a column in those tables having name as QUANTITY. I want to copy the formula of first cell of this column and paste it down to remaining cells of this column except for the last cell which belongs to total row. Sheets RULES and STATS are not to be modified in any respect. I need a VBA/Macro for this and if possible with comments for instructions for understanding of a layman. I am attaching the file.
Try this code: (file also attached):
Option Explicit
Sub ClearData()
Dim Wks As Worksheet, tbl As ListObject, ColumnsToClear As String, Cell As Range, i As Byte
ColumnsToClear = "EntryDate, Close Date, SCRIP, LONG/SHORT, EntryPrice, ExitPrice, R1, R2, R3, R4, R5, NTR1, NTR2, RightSL, Continuation"
For Each Wks In ThisWorkbook.Worksheets
If Not Wks.Name Like "RULES" And Not Wks.Name Like "STATS" Then
Set tbl = GetTable(Wks)
If Not tbl Is Nothing Then
'clear columns
If Not tbl.DataBodyRange Is Nothing Then
For Each Cell In tbl.HeaderRowRange
If InStr(1, ColumnsToClear, Cell.Value, vbTextCompare) > 0 Then
tbl.ListColumns(Cell.Value).DataBodyRange.ClearContents
End If
Next Cell
If TableColumnExists(tbl, "QUANTITY") Then
tbl.ListColumns("QUANTITY").DataBodyRange.Formula = tbl.ListColumns("QUANTITY").DataBodyRange.Cells(1).Formula
'or, set the formula in code
'tbl.ListColumns("QUANTITY").DataBodyRange.Formula = "=IF(OR([@[LONG/SHORT]]=""S"", [@[LONG/SHORT]]=""L""), 50, 200)"
End If
End If
'other ranges
For i = 2 To 4
Wks.Range("AC" & i & ":AD" & i).ClearContents
Next i
Else
MsgBox "Target table was not found in " & Wks.Name
End If
End If
Next Wks
End Sub
Function GetTable(ByVal Wks As Worksheet) As ListObject
On Error Resume Next
Set GetTable = Wks.ListObjects(Left(Wks.Name, 3))
End Function
Function TableColumnExists(ByVal tbl As ListObject, ByVal ColName As String) As Boolean
On Error Resume Next
TableColumnExists = tbl.ListColumns(ColName).Index > 0
End Function
Thanks Catalin. Code works fine. The file you have attached is corrupted but I executed the code in my file and it has worked like wonders. Thanks again.
Since all tables contain data body range hence don't you think that the code 'If Not tbl.DataBodyRange Is Nothing Then' is redundant?
Cells AC2, AD2, AC3, AD3, AC4, AD4 lie outside of the table range hence I think the For loop for clearing these cells should not be inside the If condition of checking table that is outside of 'If Not tbl Is Nothing Then'. So the Else block of above if condition to display message 'Target table was not found.... should be above the For loop for clearing the cells AC2, AD2, AC3, AD3, AC4 AD4. What do you think?
If there is no table then error will occur after the code inside the function GetTable(),
Set GetTable = Wks.ListObjects(Left(Wks.Name, 3)) gets executed.
After which it will pass the control to Set tbl = GetTable(Wks) where it will assign 'Nothing' to tbl and for which we need the error handling code 'On Error Resume Next'. Hence don't you think that this error handling code should be placed after the code 'Set GetTable = Wks.ListObjects(Left(Wks.Name, 3))' so that it will follow the error code after the error has encountered which obviously is after it executes the code 'Set GetTable = Wks.ListObjects(Left(Wks.Name, 3))'. However you have placed that error handling code before that code What do you think?
Yes, you can move AC:AD cells processing outside the "If Not tbl Is Nothing Then...End If" condition.
"'If Not tbl.DataBodyRange Is Nothing Then' is redundant": it's a safety measure, these are never redundant 🙂
Disabling error handling before a loop is totally different than disabling/handling an error WITHIN a loop.
See this: https://www.myonlinetraininghub.com/error-handling-in-vba
Finally: "The file you have attached is corrupted": This is a microsoft bug I know for years, in fact it's the vba project that gets corrupted. I "invented" a crazy method to restore the vba project when this happens:
- do not enable macros when opening the file (close and rename if you did)
- open any code module, add a space or a new line or make any irrelevant change
- save the file and open it again, it will work this time.
