Delete fixed Rows and Columns

**brumar** · 04-06-2014, 10:41 AM

I have a daily variance report I export to Excel and every day I have to delete certain rows and the certain columns before I can run a macros to delete all the zero value rows. I can not run this macros before the time as the the rows and columns that I delete contain zero values and thus I will lose most of my work if I run the Macros before hand.

The rows and columns are set, so the exact same rows and columns are deleted every day. So I need a macros to delete the set rows and then another macros to delete the columns. I'm not very good with programming so any help will help.

Thanks in advance...

**mikeTRON** · 04-06-2014, 11:41 AM

Ok so luckily this should be a very easy thing but let me start with a few questions.

What rows need to be deleted?
What columns need to be deleted?
If you already have a macro to run AFTER you delete said columns and rows, why not include the code in the beginning of THAT code so you can just run one macro?

**brumar** · 04-06-2014, 02:36 PM

I can give you the list off rows to be removed but it's a couple if pages (30) worth. I thought I would save you the hard word and add it if I knew where or how to include it in the code. The rows will be specific lines number and rows so it has no barring on the content of the cells.

If we can add it to once macros it will be great. Just please keep in mind that I'm not the smartest peanut in the packet when it comes to programming.

Sub HideRowsByZero()
'Update 20131107
Dim Rng As Range
Dim WorkRng As Range
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
For Each Rng In WorkRng
If Rng.Value = "0" Then
Rng.EntireRow.Hidden = True
End If
Next
End Sub

**mikeTRON** · 04-06-2014, 09:17 PM

Range("A:D, H:K").delete 'This deletes columns A through D and H through K

Sorry my phone was going nuts on me and wouldn't let me edit this post so I gave up on it.

**brumar** · 04-07-2014, 08:29 AM

Thank you very much for the info. Where do I insert this code in the existing macros I provided above...

**mikeTRON** · 04-07-2014, 02:36 PM

So to delete rows or columns you have a few ways to do it.

You can use the Range, Rows or Columns as shown below.

Columns ("A:C").delete

Rows("5:6").delete

Range("A:A").delete or
Range("1:1").delete

So lets assume you want to delete row 5 and 10 and columns E and J use the following code:

Sub HideRowsByZero()
'Update 20131107

'Declare Variables
Dim Rng As Range
Dim WorkRng As Range

'Delete Rows and Columns Before Beginning Loop
Range("A:A,J:J").delete 
Range("5:5, 10:10").delete 

On Error Resume Next 'If error skip to next in loop

'Define Variables
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)

'Begin Loop
For Each Rng In WorkRng
     If Rng.Value = "0" Then
          Rng.EntireRow.Hidden = True
     End If
Next

End Sub

Delete fixed Rows and Columns

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Delete fixed Rows and Columns

Re: Delete fixed Rows and Columns

Re: Delete fixed Rows and Columns

Re: Delete fixed Rows and Columns

Re: Delete fixed Rows and Columns

Re: Delete fixed Rows and Columns

Thread Information

Users Browsing this Thread

Similar Threads

Copying a formula with fixed rows but different columns

how to create a range with fixed columns and changing rows

Lookup from fixed rows and dynamic columns

Dynamic Range of Columns with fixed Rows

[SOLVED] Excel 2000 - Fixed size columns and rows?

Bookmarks

Bookmarks

Posting Permissions