Clear contents

[Klartigue]

I need to write a code that says for rows in which the cell in column A is blank, then clear the contents in the cells from column A to Column N.

So if the value in cell A is "", then for that row clear contents from A to N.

Can you help?

[Klartigue]

Perhaps something like this? Although the below code gets an error...



Sub test()
Dim Lastrow As Long
Dim i As Long
With ActiveSheet
If Cells(i, "A").Value = "" Then
.Rows(i).Clearcontents
End If
End With
End Sub

[SamT]

Klartique, Ha,ha, we have new tags. Use the # button for the new code tags. Or replace "VBA" with "CODE" in the square brackets

Actually your example is a good algorithm, but it would clear the contents of the entire row. If that is what you want I leave it to you to modify the below example, which will only clear the row from A to N

Option Explicit

Sub test()

Dim LastCell As Range 'A range is easier to assign to another Range than a Row number
Dim Cel As Range

LastCell = Cells(Rows.Count, 1).End(xlUp) 'First used Cell above Cell(Bottom Row, First Column)

With ActiveSheet
For Each Cel In Range(Range("A1"), LastCell)
If Trim(Cel.Text) = "" Then 'Trim checks for invisible spaces
Cel.Resize(1, 14).ClearContents 'Resize Range(Cel) to 1 row and 14 columns
End If
Next Cel
End With

End Sub

If the code is in the Worksheet code page, it does not need the "With ActiveSheet" parts.

[Klartigue]

Pretty much, I have a block of information starting in row 1 and ending in another row, it will always vary how many rows down it goes. However, It will always fill columns A-N but you never know how many rows down it will fill. All the other cells will be blank, however, when I save this in notepad form, there are some weird contents that appear as if they were in the cells so it helps to clear contents for all blank cells in my excel sheet. Therefore, for all cells below the lastrow (determined by column A), even though to the visible eye they are all blank, I just need to select the area starting in the first row after the lastrow and going to down to row 1000 over to column N and clear contents.

[SamT]

Use
LastCell (From Above) Offset(1, 0).Resize(Rows.Count - LastCell.Row, 14).ClearContents

[Klartigue]

Sub Macro4()
Dim Lastrow As Long
Dim i As Long
With ActiveSheet

Lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = 2 To Lastrow

If .Cells(i, "B").Value = "" Then

With Range(.Cells(i, "A"), .Cells(i, "N")).Select
Selection.Clearcontents

End With

End If

Next i

End With

End Sub



I have this code which clears the contents within the parameters of row 2 to lastrow. However, I need to change that to have it clear the contents for all cells NOT included in the parameters of row 2 to lastrow. Any ideas?

[SamT]

Sub ClearAllBut()
ActiveSheet.Range(Range("O1"), Cells(Rows.Count, Columns.Count)).ClearContents
End Sub

Alternately


Sub ClearAllBut()
ActiveSheet.Range("O1").Resize(Rows.Count, Columns.Count).ClearContents
End Sub

[Klartigue]

I get an error saying application defined or object defined error

[snb]

sub M_snb()
columns(1).specialcells(4).entirerow.delete
end sub

or


sub M_snb()
intersect(columns(1).specialcells(4).entirerow, columns(1).resize(,14)).clearcontents
end sub