Excel Macro Question using if then loop to hide rows

[5ybarite]

I have a report that I have to generate weekly from another application. This other app will kick things out to Excel; what I want to do is automate the formatting process.

Essentially this consists of creating a new worksheet based on the value of cell B (there should only be 4 values) and then within each worksheet, sorting and hiding rows based on a comparison between 2 pairs of cells (e.g. a1:b1, and a2:b2) (W2K, XL2K2)

I did search the FAQ's, and I found a close approximation for what I was doing but I can't make it work since I no longer speak VBA. Can anyone give advice on how to beat this Type Mismatch error?

Sub HideRows()
Dim StartRow As Integer, EndRow As Integer
Dim TargetCol As String, TargetCol1 As String, x As Integer
StartRow = 1
EndRow = 150
TargetCol = "A"
TargetCol1 = "D"
For x = StartRow To EndRow
   If Range(TargetCol & x, TargetCol1 & x).Value = Range(TargetCol & x + 1, TargetCol1 & x + 1).Value Then
      Range(TargetCol & x + 1).EntireRow.Hidden = True
   End If
Next x
End Sub

 

[PHV]

this Type Mismatch error
On which line ?
In which VBA project is the HideRows procedure ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[5ybarite]

If Range(TargetCol & x, TargetCol1 & x).Value = Range(TargetCol & x + 1, TargetCol1 & x + 1).Value Then

This is all the farther I've gotten. Everytime I run it, I get that error.

Project? It's in a project module specific to that worksheet. I'm still learning, so I don't know if that's what you're after. Unless you mean the (General) thing.

Thanks,
~5ybarite

 

[PHV]

If Range(TargetCol & x & ":" & TargetCol1 & x).Value = Range(TargetCol & (x + 1) & ":" & TargetCol1 & (x + 1)).Value Then

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[5ybarite]

That still yields a "Run-Time error '13': Type mismatch" message on that same line. Any other ideas?

Thanks for your help BTW

Thanks,

~5ybarite

 

[PHV]

Just reread your post.
I don't think you can compare the value of 2 multi-cells ranges.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244