CODEWITHSUNDEEP
vbaexcel
sc1324
sc1324

Reputation: 600

Check if cells across is blank then hide a header vba

I have some data in excel with headers in column B and data from column C, starting in row 2. In row 15, it may or may not have a numerical value. My task is to check from cell C15 to the end of the last column, maybe F15, maybe Z15, that if they are blanks across, I can delete B15, the header for that row.

Now, I can't just check if all values in row 15 is blank since I have the header in cell B15. I get weekly data so that is why last column vary.

What I need is if I have data, in row 15, but not the same across, I keep my header B15, if I don't have any data or if I have the same data across. I want to delete my header, B15.

I wrote some vba codes but it's not producing desire results.

Sub test()
Dim LR3, z As Long
Dim ws As Worksheet

Set ws = Worksheets("wc")


 LR3 = ws.Cells(2, Columns.Count).End(xlToLeft).Column
 For z = 3 To LR3
    'start position
    If ws.Cells(15, z).Value = ws.Cells(15, z + 1).Value Then
        ws.Cells(15, 2).Value = ""
     End If
  Next z
 End Sub

Upvotes: 1

Views: 228

Answers (2)

stefan
stefan

Reputation: 239

I don't really understand your task but I tried to fix your loop. Because I thought you want to iterate through the last row starting from column c, is that right? 

Option Explicit

Sub test()
    Dim LC3, z As Long
    Dim ws As Worksheet
    Dim startColumn As Integer
    Dim checkRow As Integer
    Dim headerColumn As Integer
    Dim allTheSame As Boolean
    Dim allEmpty As Boolean

    Set ws = Worksheets("wc")
    LC3 = ws.Cells(ws.UsedRange.Rows.Count, 2).End(xlToRight).Column
    startColumn = 3
    checkRow = 15
    headerColumn = 2

'    'if first column is blank delete the header
'    If ws.Cells(checkRow, startColumn).Value = "" Then
'        ws.Cells(checkRow, headerColumn).Value = ""
'        Exit Sub
'    End If
'
    allTheSame = True
    allEmpty = True

    For z = startColumn To LC3
        'if any column is blank delete the header
'        If ws.Cells(checkRow, z).Value = "" Then
'            ws.Cells(checkRow, headerColumn).Value = ""
'            'Exit For
'        End If
        'if one is not the same delete header
'        If z + 1 <= LC3 Then
'            If ws.Cells(checkRow, z).Value <> ws.Cells(checkRow, z + 1).Value Then
'                ws.Cells(checkRow, headerColumn).Value = ""
'                Exit For
'            End If
'        End If
        'if any column is blank delete the header
        If ws.Cells(checkRow, z).Value <> "" Then
            allEmpty = False
            'Exit For
        End If
        'if all are the same, delete header
        If z + 1 <= LC3 Then
            If ws.Cells(checkRow, z).Value <> ws.Cells(checkRow, z + 1).Value Then
                allTheSame = False
            End If
        End If
    Next z
    If allTheSame Or allEmpty Then
        ws.Cells(checkRow, headerColumn).Value = ""
    End If
End Sub

Upvotes: 1

Harassed Dad
Harassed Dad

Reputation: 4704

 If Application.Worksheetfunction.CountA("C" & z & ":Z" & z) = 0 then

'Row z has no entries between C and Z

Upvotes: 0

Related Questions