ragav_in
Board Regular
- Joined
- Feb 13, 2006
- Messages
- 89
- Office Version
- 365
- 2016
- Platform
- Windows
Dear All,
I have a specific request to delete the text from the Row 3 onwards, as the top 2 rows are considered to be template text and needs to be retained. I have written the below code which performs the deletion as expected. However the same macro when run on the sheet when it has only the first 2 rows of data, deletes the 2nd row which it should not do. Can someone help me identify on what am I doing wrong here. The expectation here is that the macro should always delete text from Row 3 onwards irrespective if there is text or not in the sheet. First 2 columns should not be deleted.
I thank everyone who takes time and effort to look into this and provides me some suggestion.
Thanks
ragav_in
I have a specific request to delete the text from the Row 3 onwards, as the top 2 rows are considered to be template text and needs to be retained. I have written the below code which performs the deletion as expected. However the same macro when run on the sheet when it has only the first 2 rows of data, deletes the 2nd row which it should not do. Can someone help me identify on what am I doing wrong here. The expectation here is that the macro should always delete text from Row 3 onwards irrespective if there is text or not in the sheet. First 2 columns should not be deleted.
List of Reports | ||||
Column 1 | Column 2 | Column 3 | Column 4 | Column 5 |
Text 1 | Text 2 | Text 3 | Text 4 | Text 5 |
Text 6 | Text 7 | Text 8 | Text 9 | Text 10 |
VBA Code:
Sub ClearFilledCells()
Dim Lr, Lc As Long, sht As Worksheet, firstcell As Range
Set sht = ThisWorkbook.ActiveSheet
Set firstcell = Range("A3")
If ActiveSheet.Name <> "MySheet" Then
Lr = Range("A3").SpecialCells(xlCellTypeLastCell).row
Lc = Range("A3").SpecialCells(xlCellTypeLastCell).Column
sht.Range(firstcell, sht.Cells(Lr, Lc)).Select
Selection.ClearContents
Selection.ClearFormats
Range("A1").Select
Else: MsgBox "Data from MySheet cannot be deleted"
End If
End Sub
I thank everyone who takes time and effort to look into this and provides me some suggestion.
Thanks
ragav_in