Change a section of code when calling it

Eddy2

New Member
Joined
Mar 2, 2017
Messages
27
Hi
I have a simple code i want to call in several occasions. Is it possible to change "qwr" when calling it?

Code:
ActiveSheet.ListObjects(1).ListColumns("qwr").DataBodyRange.ClearContents
Cheers!
 

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
43,708
Office Version
365
Platform
Windows
You have a code something like this
Code:
Sub ClearTableColumn(col_Header As String)
  ActiveSheet.ListObjects(1).ListColumns(col_Header).DataBodyRange.ClearContents
End Sub
Then when you want to clear a column you use, say
Code:
Sub test()
  ClearTableColumn "qwr"
End Sub
.. and next time it might be
Code:
Sub test()
  ClearTableColumn "abc def"
End Sub
 

Eddy2

New Member
Joined
Mar 2, 2017
Messages
27
This works. Thanks!


You have a code something like this
Code:
Sub ClearTableColumn(col_Header As String)
  ActiveSheet.ListObjects(1).ListColumns(col_Header).DataBodyRange.ClearContents
End Sub
Then when you want to clear a column you use, say
Code:
Sub test()
  ClearTableColumn "qwr"
End Sub
.. and next time it might be
Code:
Sub test()
  ClearTableColumn "abc def"
End Sub
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
43,708
Office Version
365
Platform
Windows
This works. Thanks!
You are welcome.

I was thinking that my "use" examples weren't really very good. This one might be better.
Code:
Sub test()
  Dim ColsToClear As Variant, itm As Variant
  
  ColsToClear = Array("qwr", "abc def", "Date")
  For Each itm In ColsToClear
    ClearTableColumn CStr(itm)
  Next itm
End Sub
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
43,708
Office Version
365
Platform
Windows
.. and it didn't have to be in a separate routine, that was just one way of doing it. You could also have a structure like this
Code:
Sub test2()
  Dim ColsToClear As Variant, itm As Variant
  
  ColsToClear = Array("qwr", "abc def", "Date")
  For Each itm In ColsToClear
    ActiveSheet.ListObjects(1).ListColumns(itm).DataBodyRange.ClearContents
  Next itm
End Sub
 
Last edited:

Eddy2

New Member
Joined
Mar 2, 2017
Messages
27
Even Better. Cheers!

.. and it didn't have to be in a separate routine, that was just one way of doing it. You could also have a structure like this
Code:
Sub test2()
  Dim ColsToClear As Variant, itm As Variant
  
  ColsToClear = Array("qwr", "abc def", "Date")
  For Each itm In ColsToClear
    ActiveSheet.ListObjects(1).ListColumns(itm).DataBodyRange.ClearContents
  Next itm
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,090,410
Messages
5,414,242
Members
403,522
Latest member
Abel_excel

This Week's Hot Topics

Top