Deleting Columns

[josros60]

Hi,

I have 12 Sheets representing each month and are hidden .

And each sheet have current and previous month values for 2021.

And I want to prepare by clearing values.

Ex. February sheet Also has previous month which is January the values in January is a formula just want to delete current month values.

Can I do this using VBA?

Thank you

 

[Saurabhj]

Hi Josros60,

Is it possible to share the sheet and explain. It would help to provide solution quickly.

Thanks,
Saurabh

 

[josros60]

each sheet has previous month and current month but previous is a formula current enter manually what I want is to delete all values for all current column the enter manually in each month.
here sample spreadsheet (Will not let me select minisheet the adding giving an error 1004 message " Method GoTo of object_Application failed", that's why pasting table.

SAGE ID	Vendor Name	Account Number	Dec	Jan
M.A.D.	M.A.D.E. MEDIA INC	M.A.D.	#REF!
MASAHM	MASROOR AHMAD LONE	MASAHM	#REF!
MDM BU	MDM BUSINESS SOLUTIONS	MDM BU	#REF!
MELTWA	MELTWATER SOCIAL SOLUTIONS	MELTWA	#REF!
METASWIT	METASWITCH NETWORKS	METASWIT	#REF!
METASCAD	METASWITCH NETWORKS LTD.	METASCAD	#REF!
METCAL	METCALFE REALTY COMPANY LIMITED	METCAL	#REF!
MICROPEP	MICROPEER SOLUTIONS	MICROPEP	#REF!
MICROS	MICROSOFT ONLINE INC.	MICROS	#REF!
MLT AI	MLT AIKINS LLP	MLT AI	#REF!
MODERN	MODERN NIAGARA BUILDING SERVICES A DIV OF MODERN NIAGARA TOR	MODERN	#REF!
SHUKLA	MOHIT SHUKLA	SHUKLA	#REF!
MOORE	MOORE CANADA CORPORATION (Marketing)	MOORE	#REF!
NETALI	NETALICO LLC	NETALI	#REF!
NETTOY	NETTOYEUR LAPLANTE CLEANING INC.	NETTOY	#REF!
NTIRET	NTIRETY INC	NTIRET	#REF!

thank you

 

[Saurabhj]

Hi,

As per my understanding you would like to delete the last column having current month data.

Is the current month always in the last column of the sheet?

Thanks,
Saurabh

 

[josros60]

Yes.

Thank you.

 

[breynolds0431]

If you'd like to delete the last column of any specific sheet, try the following, which has two options:

Sub Gone()

Dim ws As Worksheet: Set ws = ThisWorkbook.Sheets("Sheet1") 'Modify as needed
Dim lCol As Long: lCol = ws.UsedRange.Columns(ws.UsedRange.Columns.Count).Column

  'option 1 (deletes column and shifts all subsequent data left
    ws.Columns(lCol).Delete Shift:=xlToLeft
 
  'option 2 (clears column contents and leaves column in place)
    ws.Columns(lCol).ClearContents

End Sub

 

[Saurabhj]

Hi,

To delete last column from all sheets, use below code:

Sub delColumn()
Dim totalSheets As Integer, colToDel As Integer, shNum As Integer

totalSheets = ThisWorkbook.Sheets.Count

For shNum = 1 To totalSheets
    colToDel = Sheets(shNum).Cells(1, Columns.Count).End(xlToLeft).Column
    Sheets(shNum).Columns(colToDel).Delete
Next
End Sub

 

[josros60]

Sorry my English no that good just to clear the contents no the columns.

Thank you

 

[Peter_SSs]

Some assumptions that I have made. Please give details of any that are not correct assumptions.

• The worksheet names are full month names. eg 'January', not 'Jan' or '1' etc
• The data is part of a formal Excel table (ListObject) as it looks like that from the colour banding in your XL2BB sheets
• The column to clear is the final column in the ListObject table
• You do not want the heading from that column deleted

If all of those are correct, then try this with a copy of your workbook.

Sub Clear_Column()
  Dim i As Long
  
  For i = 1 To 12
    With Sheets(MonthName(i)).ListObjects(1).DataBodyRange
      .Columns(.Columns.Count).ClearContents
    End With
  Next i
End Sub