In excel sheet I used record macro to filter data.After filter data I removed it and I got pending record.But when I select all record to remove then this also removed header.I want not to remove header.
eg I have data I want to delete all record which exist between 0 to 1.I applied between condition.I got all record then I select ctrl plus A to select all record in this my header also select and removed.I just want remove only data not header.Can anyone suggest me what I need to do…?
I am attaching my macro function as well as excel file in attachment
While doing this keep the cursor box in A2 and do the selection so that the header won’t get selected and only the records will get selected
Cheers @Aditya10989
Try to select Visible data only
u can select those instead of ctrl+A
Use ALT+:
Code will be
Selection.SpecialCells(xlCellTypeVisible).Select
for that click on header then use offset to get below cell then select that entire row then cltr+shift+down
then ALT+:
then delete
For Reference moving one cell down of visible cell:
Sub NextVisible()
Dim r As Range
Set r = ActiveCell
For i = 1 To Rows.Count
Set r = r.Offset(1, 0)
If r.EntireRow.Hidden = False Then
r.Select
Exit Sub
End If
Next
End Sub
Thanks for reply.yes I also try this I used ctrl plus shift plus end this will select all row exclude header but when I try to delete then it did’t delete first row and header
When I directly click on delete then it delete all rows but I want to delete using right click to delete entire row but when I try to delete entire row I got two record mention in attachment
Directly click on delete it will work but in this case I am removing record using filter and got record pending other all delete.But this process on remove data rows blank are still show.So I want to delete record using right click and delete entire row
Thanks but using ctrl +shift+down
this will not automate. Because I have different different excel sheet some file contian 1 lakh rows and some contain 10 lakhs rows.So rows are not fixed.I create a macro which select all records right now I am using ctrl + A and delete filter record.then using in ui path invoke macro for execute
After Ctrl+A deselect header and then Use Alt+: for visible selection then delete them