Hide cells based on value

[Farley945]

Hi All,

I have looked on similar post but couldn't find the answer to my query

on the below code, instead of deleting the entire row, I want the row to hide. Can anyone help with this?

Thank you in advance

Sub commandbutton1_click()
Dim x As Integer
Dim y As Integer
Dim i As Integer
Dim shSource As Worksheet
Dim shTarget1 As Worksheet
Dim shTarget2 As Worksheet


Set shSource = ThisWorkbook.Sheets("Phase Bad Debt Schedule Compan")
Set shTarget1 = ThisWorkbook.Sheets("Paid")


If shTarget1.Cells(7, 32).Value = "" Then
x = 5
Else
x = shTarget1.Cells(7, 32).CurrentRegion.Rows.Count + 5
End If



i = 7


Do Until shSource.Cells(i, 32) = ""
    If shSource.Cells(i, 32).Value = "Paid" Then
    shSource.Rows(i).Copy
    shTarget1.Cells(i, 1).PasteSpecial Paste:=xlPasteValues
    shSource.Rows(i).Delete
    x = x + 1
    GoTo Line1
    
    End If
i = i + 1


Line1: Loop


End Sub

 

[Fluff]

Hi & welcome to MrExcel.
Change

shSource.Rows(i).Delete

to

shSource.Rows(i).Hidden=True

 

[Farley945]

Hi & welcome to MrExcel.
Change

shSource.Rows(i).Delete

to

shSource.Rows(i).Hidden=True

Hi Fluff,

I tried that before but it didn't work.

The screen just flashes, after 2 minutes only the first row was hidden

 

[Farley945]

Also instead of copy and paste, I would ideally need to cut and paste if possible please

 

[Fluff]

With the change I suggested also get rid of these two lines

    x = x + 1
    GoTo Line1

 

[Farley945]

With the change I suggested also get rid of these two lines

    x = x + 1
    GoTo Line1

Thank you, that perfectly, I don't suppose you saw my other message regarding; changing the copy and paste to cut and paste?

 

[Fluff]

How about

Do Until shSource.Cells(i, 2) = ""
    If shSource.Cells(i, 2).Value = "No" Then
    shSource.Rows(i).Copy
    shTarget1.Cells(i, 1).PasteSpecial xlPasteValues
    shSource.Rows(i).Clear
    shSource.Rows(i).Hidden = True
    End If

 

[Farley945]

Hi Fluff,

That works perfectly, thank you.

My only issue is now, the button only works once, whereas I want it to work every time it's pressed as the figures I use are updated every other day.

See code below:

Sub commandbutton1_click()
Dim x As Integer
Dim y As Integer
Dim i As Integer
Dim shSource As Worksheet
Dim shTarget1 As Worksheet
Dim shTarget2 As Worksheet


Set shSource = ThisWorkbook.Sheets("Phase Bad Debt Schedule Compan")
Set shTarget1 = ThisWorkbook.Sheets("Paid")


If shTarget1.Cells(7, 32).Value = "" Then
x = 5
Else
x = shTarget1.Cells(7, 32).CurrentRegion.Rows.Count + 5
End If



i = 7


Do Until shSource.Cells(i, 32) = ""
    If shSource.Cells(i, 32).Value = "Paid" Then
    shSource.Rows(i).Copy
    shTarget1.Cells(i, 1).PasteSpecial Paste:=xlPasteValues
    shSource.Rows(i).Clear
    shSource.Rows(i).Hidden = True
       
    End If
i = i + 1


Line1: Loop


End Sub

 

[Fluff]

What do you mean it only works once?

 

[Farley945]

After I have clicked the button it works but when I up date my data, it won't work after that