Delete Selected Columns & Rows at the end

[mrc44]

I need a way to delete all the columns & rows through the end of the worksheet. Lets say I want to keep 1000 rows and 200 columns and want to delete all the rest.

I will add more columns and rows as needed and style them. Is there short way or vba for this? where I can set the range of columns and rows? please.

I know about Find&Select method, but it is not exactly same, and it is slow and also it selects the empty cells among the used columns and rows. Thanks.

 

[bugmonsta]

Heres a very quick idea - it will ask you the column to delete from (its included in the deletion) and then range select all the columns and delete. The it will select your chosen column cell 1

next you are asked you what row to delete from and follow the same rules as above, selects all rows to the bottom and deletes them, then selects your row cell 1.

I created this by macro recording and then changing the text in Columns("H:H").Select to Columns(ColValue & ":" & ColValue).Select

and then the same for the row.

it may not be the finest bit of vba and I am sure someone has a better way of doing this but it should get you started.

Sub Macro1()
'

Dim ColValue As Variant
Dim RowValue As Variant
On Error GoTo TEnd

ColValue = InputBox("What column to you want to delete FROM?" & vbCr & vbCr & "Warning, this could take some time!")
    MsgBox ColValue
    
    Columns(ColValue & ":" & ColValue).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Delete Shift:=xlToLeft
    Range(ColValue & "1").Select
     
RowValue = InputBox("What row to you want to delete FROM?" & vbCr & vbCr & "Warning, this could take some time!")
    Rows(RowValue & ":" & RowValue).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Delete Shift:=xlUp
    Range(RowValue & "1").Select
TEnd:
End Sub

 

[mrc44]

Thanks for your suggestion. I tried it but it didn't delete the selected columns or rows.

 

[bugmonsta]

When you say delete, do you mean to hide them completely so they do not show on screen? The VBA I coded was just to delete the rows/columns, Excel will always put new ones back in place (so technically my code will purge the data in the rows and columns).

If you want to hide rows/columns then that can be done as well, let us know.

 

[mrc44]

What I want to do is to delete all the extra rows /columns completely (not hide) like in spreadsheets. I want just the right amount of columns and rows to appear in worksheet if possible. So when I try to style a certain range of columns or rows, i won't have to select a specific range.