How to remove blank space

[Vishaal]

Hi,

I have the following sheet

Col A	Col B	Col C	Col D
S.No.	Name	Attendence	Hour
1	John	P	5
2	Nick	P	6
3		P	7
4		P	2
5	Raghu	P	4
6	Ravi	P	4
7	Rozi	P	5
8		P	9
9	Matty	P	8
10		P	5
11	Saurabh	P	5
12	Hanery	P	5

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

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.