VBA Deselect Rows and Columns from worksheet selection

Question

I have a bunch of .csv data reports i need to clean up in Excel.

I have been trying to write a macro that would select everything, then deselect a few specific rows and columns from the selection.

I don't need the code to go any further than that as i have another macro that works for deleting everything.

The nice thing is that the data im removing is consistent so i can hard code what needs to go bye bye.

Ive got some C# exposure but not VBA and particularly to MS Excel. I found some code I have been trying to adapt but am not getting it to do anything or just error out. At this point I was thinking to get it to do one line, then figure out how to remove the others.

Can I get some pointers on how to Select everything minus A row or column?

    Sub Macro1()
'
' Macro1 Macro
' report format
'

    Dim rng As Range
Dim InputRng As Range
Dim DeleteRng As Range
Dim OutRng As Range
' xTitleId = "KutoolsforExcel"
Set InputRng = Application.Selection
' Set InputRng = Application.InputBox("Range :", xTitleId, InputRng.Address, Type:=8)
Set DeleteRng = Application.Rows(7)
For Each rng In InputRng
    If Application.Intersect(rng, DeleteRng) Is Nothing Then
        If OutRng Is Nothing Then
            Set OutRng = rng
        Else
            Set OutRng = Application.Union(OutRng, rng)
        End If
    End If
Next
OutRng.Select

'
End Sub

[You normally don't need to `Select`](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). Maybe post your delete code, explain which rows need deleted, and we can optimize it. — BigBen, Jul 28 '20 at 20:56
forgive my ignorance. I don't understand well enough what I am doing and I just need to do about 50 reports and probably never do this again. Just trying to do less Ctrl+Click.. Anyway, once i have everything selected im just keyboard short cutting with a recorded keyboard macro to delete and shift cells left. What i am doing is selecting everything, Deselecting Rows 1 and 2, then Deselecting columns A,B, D,E and G. Then i hit the delete macro key. — eatumup, Jul 28 '20 at 21:38

score 0 · Answer 1 · answered Jul 29 '20 at 17:44

So i feel silly as I completely overlooked that Excel can attempt to auto record macros. I gave it a whirl and it did what I needed to do.

here is the auto generated code to produce the end result I needed. It only highlights the desired cells. From there I can use keyboard shortcuts to format the sheet.

I absolutely don't understand what it did but here it is.

   Sub work()
'
' work Macro
'

'
    Range("M3:XFD1048576,F3:J1048576,C3:C1048576").Select
    Range("C3").Activate
End Sub

score -1 · Answer 2 · answered Jul 28 '20 at 22:36

-1

Sub Macro() Dim wb As Workbook Set wb = ThisWorkbook 'Consider column(2) as B column wb.Worksheets("Sheet1").Columns(2).EntireColumn.Delete

answered Jul 28 '20 at 22:36

Deeraj

11
4

thanks. That deletes the column but the end result im really looking for is just at the end of the macro to have all cells of the spreadsheet highlighted minus those few rows and columns exempted. – eatumup Jul 29 '20 at 17:34
Okay i m geeting the point here. Firstly you want the cells to be highlighted i mean how you will decide that which cells to be highlighted(on what basis) is there any criteria for that and for the rows which needs to be exempted there should also be some criteria for that can you please tell me on what basis you will do that if you do it manually? – Deeraj Jul 31 '20 at 08:45

VBA Deselect Rows and Columns from worksheet selection

2 Answers2