Delete Visible Cells from Column N onwards

pkew22

New Member
Joined
Aug 30, 2013
Messages
38
I have data up to Column Z (this can change) and the data starts in row 6. Row 5 contains headers. I have filtered the data. I would like to delete data from all visible cells from Column J to the last row in the last column. (I would like to keep ALL data in Columns A:I and I would also like to keep the data hidden by the filter from Column J through the last column.) How can I do this with VBA? Thank you.
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
Hello,

do you mean delete or clear contents?

if clear contents try

Code:
Sub DELETE_VISIBLE()
    For MY_COLS = 10 To ActiveSheet.UsedRange.Columns.Count 'Cells(5, Cells(5, Columns.Count).End(toleft)).Column
        For MY_ROWS = Cells(Rows.Count, MY_COLS).End(xlUp).Row To 6 Step -1
            If Rows(MY_ROWS).Hidden = False Then
                Cells(MY_ROWS, MY_COLS).ClearContents
            End If
        Next MY_ROWS
    Next MY_COLS
End Sub

if delete this will be a bit more difficult, would you want the cells to shift up? or which way?
 
Upvote 0
Solution
Thank you. I meant clear contents. This is very useful. My sheet has changed somewhat but your code is so clear, I should be able to amend it a bit.

Once again, thank you.
 
Upvote 0
It sounds like you data starts in column A. Assuming there is also something in row 1 somewhere on the sheet, you should be able to delete all the values at once rather than looping individually through rows & columns.

If my assumption about row 1 is correct, give this a try in a copy of your workbook.
Code:
Sub DelVis()
  ActiveSheet.UsedRange.Offset(5, 9).SpecialCells(xlVisible).ClearContents
End Sub
 
Upvote 0

Forum statistics

Threads
1,223,227
Messages
6,170,853
Members
452,361
Latest member
d3ad3y3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top