If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
VB Code to automate a sheet clean-up
Hi all
I am fearly useless when it comes to coding in VB as the code below will prove Before I give the code I thought I'd share the 'what' I want to achieve. I am dumping huge amount of data into excel from an external database and then want to clean the shpreadsheet removing all the irrelevant clutter. By that i mean that I want to hide the rows that contain data where I have 0 as a result. But i want it as a range result. ie I select B5 to B26, check the total in =SUM and if it is equal to 0 I want to hide it. Pretty simple uh? Here is what I tried... and it dosen't work *Sub SortSheet()* Dim i As Integer Dim R As Range With ActiveSheet Set R = Range("B6:AB6") For i = 6 To 6000 If R.Value = 0 Then R.Select Selection.EntireRow.Hidden = True End If Next R End With End Sub I then tried something else thinking the range was to set... *Sub SortSheet()* Dim nRange As Integer Dim R As Range With ActiveSheet For nRange = .Range(.Cells(5, 2), .Cells(5, 28)) To _ .Range(.Cells(6000, 2), .Cells(6000, 28)) If R.Value = 0 Then R.Select Selection.EntireRow.Hidden = True End If Next nRange End With End Sub That didn't work either... Please help! I'm lost!!!!! Many thanks! --- Message posted from http://www.ExcelForum.com/ |
#2
|
|||
|
|||
VB Code to automate a sheet clean-up
Hi
not really sure what values you want to test? What rows do you want to hide what are your conditions? -- Regards Frank Kabel Frankfurt, Germany Hi all I am fearly useless when it comes to coding in VB as the code below will prove Before I give the code I thought I'd share the 'what' I want to achieve. I am dumping huge amount of data into excel from an external database and then want to clean the shpreadsheet removing all the irrelevant clutter. By that i mean that I want to hide the rows that contain data where I have 0 as a result. But i want it as a range result. ie I select B5 to B26, check the total in =SUM and if it is equal to 0 I want to hide it. Pretty simple uh? Here is what I tried... and it dosen't work *Sub SortSheet()* Dim i As Integer Dim R As Range With ActiveSheet Set R = Range("B6:AB6") For i = 6 To 6000 If R.Value = 0 Then R.Select Selection.EntireRow.Hidden = True End If Next R End With End Sub I then tried something else thinking the range was to set... *Sub SortSheet()* Dim nRange As Integer Dim R As Range With ActiveSheet For nRange = .Range(.Cells(5, 2), .Cells(5, 28)) To _ Range(.Cells(6000, 2), .Cells(6000, 28)) If R.Value = 0 Then R.Select Selection.EntireRow.Hidden = True End If Next nRange End With End Sub That didn't work either... Please help! I'm lost!!!!! Many thanks! --- Message posted from http://www.ExcelForum.com/ |
#3
|
|||
|
|||
VB Code to automate a sheet clean-up
I had a feeling i wasn't being clear sorry!!
Basically I have a set of rows and columns. In rows i have a list of products, in columns i have months. In the cells I have sales figure. What I want to be able to do is take the sum of the range January December, check if it is equal to 0 and hide it if that returns true. Then i want to do that row by row... Hope it's clearer!! --- Message posted from http://www.ExcelForum.com/ |
#4
|
|||
|
|||
VB Code to automate a sheet clean-up
Hi
try the following (column B:M contain your month names, col. A your products) Sub hide_rows() Dim RowNdx As Long Dim LastRow As Long application.screenupdating=false LastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).row For RowNdx = LastRow To 1 Step -1 if application.sum(range(cells(RowNdx,2),cells(RowNdx ,13)))=0 then Rows(RowNdx).hidden = True End If Next RowNdx application.screenupdating=true End Sub -- Regards Frank Kabel Frankfurt, Germany I had a feeling i wasn't being clear sorry!! Basically I have a set of rows and columns. In rows i have a list of products, in columns i have months. In the cells I have sales figure. What I want to be able to do is take the sum of the range January December, check if it is equal to 0 and hide it if that returns true. Then i want to do that row by row... Hope it's clearer!! --- Message posted from http://www.ExcelForum.com/ |
Thread Tools | |
Display Modes | |
|
|