Delet columns and rows

[shrikant sinnur]

dear sir,

i most of the time use excel.

i want to know that,

i want to keep columns from A to H and Rows from 1 to 55.

i want to delete rest all columns and rows and it should not generate the columns and rows again and again.

pl, help me

thanking you

shrikant

 

[erik.van.geit]

HI, shrikant,

You can not "delete" those rows adn columns.
You can hide them though. (select, rightclick and see in the menu you get)

kind regards,
Erik

 

[VoG]

Try

Sub DelColRw()
Dim LR As Long, LC As Long
LR = Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
LC = Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByColumns).Column
If LC > 8 Then Range(Cells(1, 9), Cells(1, LC)).EntireColumn.Delete
If LR > 55 Then Rows("56:" & LR).Delete
End Sub

 

[shrikant sinnur]

Thank you
Erik
for your reply is it not possible to delete col and row in any another way.

shree

 

[VoG]

Perhaps you mean this

Sub DelColRw()
Range(Cells(1, 9), Cells(1, Columns.Count)).EntireColumn.Hidden = True
Rows("56:" & Rows.Count).Hidden = True
End Sub

 

[shrikant sinnur]

VoG
hai,

i pasted your code in vb editor like this but not worked.

Private Sub Worksheet_Activate()
Dim LR As Long, LC As Long
LR = Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
LC = Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByColumns).Column
If LC > 8 Then Range(Cells(1, 9), Cells(1, LC)).EntireColumn.Delete
If LR > 55 Then Rows("56:" & LR).Delete
End Sub

tell how to use the code.

 

[VoG]

To use the code in that way you have to place it in the sheet's code module. Right click the sheet tab, select View Code and paste in the code.

 

[erik.van.geit]

you are welcome!

A worksheet has a fixed amount of columns and rows which are available for use: you can NOT change that.
You can select them and hide them.
Alternatively you can restrict the scrollarea: so even when those columns and rows stay visible the user can not "see" them on screen.

 

[Misca]

I wasn't sure if you wanted to hide or clear the rest of the columns and rows so I did a macros for both:

Sub HideColumnsAndRows()

'Hides the columns I->
Range(Columns(9), Columns(Columns.Count)).EntireColumn.Hidden = True
'Hides the rows 56->
Range(Rows(56), Rows(Rows.Count)).EntireRow.Hidden = True

End Sub

Sub ClearColumnsAndRows()

'Clears the columns I->
Range(Columns(9), Columns(Columns.Count)).Clear
'Clears the rows 56->
Range(Rows(56), Rows(Rows.Count)).Clear

End Sub

Both macros work on activesheet.

 

[shrikant sinnur]

still not working