How to remove blank space

[Vishaal]

Hi,

I have the following sheet

[TABLE="width: 274"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]Col A[/TD]
[TD]Col B[/TD]
[TD]Col C[/TD]
[TD]Col D[/TD]
[/TR]
[TR]
[TD]S.No.[/TD]
[TD]Name[/TD]
[TD]Attendence[/TD]
[TD]Hour[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]John[/TD]
[TD]P[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Nick[/TD]
[TD]P[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD]P[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD]P[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Raghu[/TD]
[TD]P[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Ravi[/TD]
[TD]P[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Rozi[/TD]
[TD]P[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD]P[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Matty[/TD]
[TD]P[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD][/TD]
[TD]P[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]Saurabh[/TD]
[TD]P[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]Hanery[/TD]
[TD]P[/TD]
[TD]5

[/TD]
[/TR]
</tbody>[/TABLE]

Now i want to remove all the entries in row 3 & 4 & 8 & 10 from the Col C to Col D

Any formula for VB Code

 

[Joe4]

Do you want to physically delete the row, so all the other rows move up, or just blank out columns C and D, leaving gaps in your data?

 

[Vishaal]

just blank out columns C and D

Pls also advice if coloumn are more than 15

 

[Joe4]

Try:

Sub MyClearColumns()

    Dim lr As Long
    Dim r As Long
    
    Application.ScreenUpdating = False

'   Find last row with data in column A
     lr = Cells(Rows.Count, "A").End(xlUp).Row
    
'   Loop through all rows
    For r = 2 To lr
'       Check to see in column B is empty
        If Cells(r, "B") = "" Then
'           Clear columns C through D
            Range(Cells(r, "C"), Cells(r, "D")).ClearContents
        End If
    Next r

    Application.ScreenUpdating = True
    
    MsgBox "Process complete"

End Sub

To change the number of columns to clear, just change the "D" here to the last column you want cleared:

Range(Cells(r, "C"), Cells(r, [COLOR=#ff0000]"D"[/COLOR])).ClearContents

 

[Rick Rothstein]

Try:

Sub MyClearColumns()

    Dim lr As Long
    Dim r As Long
    
    Application.ScreenUpdating = False

'   Find last row with data in column A
    [B][COLOR="#FF0000"][SIZE=3]lr = [/SIZE][/COLOR][/B]Cells(Rows.Count, "A").End(xlUp).Row
    
'   Loop through all rows
    For r = 2 To lr
'       Check to see in column B is empty
        If Cells(r, "B") = "" Then
'           Clear columns C through D
            Range(Cells(r, "C"), Cells(r, "D")).ClearContents
        End If
    Next r

    Application.ScreenUpdating = True
    
    MsgBox "Process complete"

End Sub

TYPO ALERT (see missing part of code highlighted in red above).

 

[Rick Rothstein]

Here is another macro that you can consider...

[table="width: 500"]
[tr]
	[td]Sub ExtendBlanksToRightForBlankNames()
  Intersect(Columns("C:[B][COLOR="#FF0000"]D[/COLOR][/B]"), Range("B1:B" & Cells(Rows.Count, "A").End(xlUp).Row).SpecialCells(xlBlanks).EntireRow).ClearContents
End Sub[/td]
[/tr]
[/table]

To change the number of columns to clear, just change the red highlighted "D" to the column letter desired.

 

[Joe4]

TYPO ALERT (see missing part of code highlighted in red above).

Thanks for catching that Rick!

I was copying similar code I was using on a different one, and missed that part.
I will amend my original code.