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]

How about

Sub commandbutton1_click()
Dim Cl As Range
Dim shSource As Worksheet
Dim shTarget1 As Worksheet


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

With shSource
   For Each Cl In .Range(.Cells(7, 32), .Cells(Rows.Count, 32).End(xlUp))
      If Cl.Value = "Paid" Then
         Cl.EntireRow.Copy
         shTarget1.Cells(Cl.Row, 1).PasteSpecial Paste:=xlPasteValues
         Cl.EntireRow.Clear
         Cl.EntireRow.Hidden = True
      End If
   Next Cl
End With

End Sub

 

[Farley945]

How about

Sub commandbutton1_click()
Dim Cl As Range
Dim shSource As Worksheet
Dim shTarget1 As Worksheet


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

With shSource
   For Each Cl In .Range(.Cells(7, 32), .Cells(Rows.Count, 32).End(xlUp))
      If Cl.Value = "Paid" Then
         Cl.EntireRow.Copy
         shTarget1.Cells(Cl.Row, 1).PasteSpecial Paste:=xlPasteValues
         Cl.EntireRow.Clear
         Cl.EntireRow.Hidden = True
      End If
   Next Cl
End With

End Sub

The code above works perfect thank you.

I've got one more questions - I have tried adding another copy and paste to the button but I don't know how to get it to work.

so I would like the search to be the same except if cl.Value = " Write Off"
Then I want it to be copied and pasted onto sheet 3 and note the "Paid" sheet

Assuming it will be
Dim shTarget2 As Worksheet

Set shTarget2 = ThisWorkbook.Sheets("Write Off")

But the rest of it I can't figure it out

Thank you in advance, sorry to be a pain.

 

[Fluff]

How about

Sub commandbutton1_click()
Dim Cl As Range
Dim shSource As Worksheet
Dim shTarget1 As Worksheet


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

With shSource
   For Each Cl In .Range(.Cells(7, 32), .Cells(Rows.Count, 32).End(xlUp))
      Select Case Cl.Value
         Case "Paid"
            Cl.EntireRow.Copy
            shTarget1.Cells(Cl.Row, 1).PasteSpecial Paste:=xlPasteValues
            Cl.EntireRow.Clear
            Cl.EntireRow.Hidden = True
         Case "Write Off"
            Cl.EntireRow.Copy
            Sheets("Write Off").Cells(Cl.Row, 1).PasteSpecial Paste:=xlPasteValues
            Cl.EntireRow.Clear
            Cl.EntireRow.Hidden = True
      End Select
   Next Cl
End With

End Sub

 

[Farley945]

How about

Sub commandbutton1_click()
Dim Cl As Range
Dim shSource As Worksheet
Dim shTarget1 As Worksheet


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

With shSource
   For Each Cl In .Range(.Cells(7, 32), .Cells(Rows.Count, 32).End(xlUp))
      Select Case Cl.Value
         Case "Paid"
            Cl.EntireRow.Copy
            shTarget1.Cells(Cl.Row, 1).PasteSpecial Paste:=xlPasteValues
            Cl.EntireRow.Clear
            Cl.EntireRow.Hidden = True
         Case "Write Off"
            Cl.EntireRow.Copy
            Sheets("Write Off").Cells(Cl.Row, 1).PasteSpecial Paste:=xlPasteValues
            Cl.EntireRow.Clear
            Cl.EntireRow.Hidden = True
      End Select
   Next Cl
End With

End Sub

So far so good, thank you, really do appreciate your help

 

[Fluff]

You're welcome & thanks for the feedback.