How to delete all rows with exact match data?

[Abbas999]

Hi,

I need a little help, I have a main excel file and i am copying 250 cells from a webpage.
There are total 5 headers.
Address - Lat - Long - East - North
When i have around 3000 cells copied to main sheet. I have the headers 12 times in it. That means

All these 5 headers " Address - Lat - Long - East - North" comes right after 250 cells. The total cells will be around 55,000 so i need a shortcut that will remove all these headers from the sheet. I tried remove duplicate function but this also removes some other data. Each 3000 cells have these headers 12 times. I want to remove complete row of these headers not just the data. I can do this by find a replace button but i want something that will exact match the headers and remove the the complete row. Thank you

 

[FormR]

Hi - you could apply an autofilter and filter the first column for "Address" and then delete the filtered rows.

 

[Abbas999]

Hi, I figure it out with find and replace feature, I was not aware of removing full row with find and replace. Anyway thank for taking time to read my thread.
I have another issue: I always have to copy 250 cells from excel sheet till 60K cells. Is there any shortcut that will select the next 250 cells and i can copy that? I mean when i press the shortcut it will select the next 250 cells and i will copy that. I now doing it by SHIFT + Page DOwn. It is very time consuming.

Hi - you could apply an autofilter and filter the first column for "Address" and then delete the filtered rows.

 

[FormR]

I have another issue:

Generally speaking - new questions deserve new threads. But why don't you copy all the cells in one go?

 

[Abbas999]

Generally speaking - new questions deserve new threads. But why don't you copy all the cells in one go?

Because the web form where i am putting the cells only accept 250 at a time. So i have to copy just 250 at a time. and yes i know it has to be posted in new thread but you can see the problem was solved within 5 minutes of posting this thread and i forgot to mention this issue in first post. Can you help in this?

 

[FormR]

You could assign this macro to a shortcut key combo - it starts from the active cell and assumes you are copying 5 columns of data and that you will manually paste to the appropriate destination.

Sub m()
With ActiveCell
    .Resize(250,5).Copy
    .Offset(250).Select
End With
End Sub

 

[Abbas999]

Please Guide how to? I never used micros and how to assign a shortcut. Please in details I'm totally a newbie here. Thanks

You could assign this macro to a shortcut key combo - it starts from the active cell and assumes you are copying 5 columns of data and that you will manually paste to the appropriate destination.

Sub m()
With ActiveCell
    .Resize(250,5).Copy
    .Offset(250).Select
End With
End Sub

 

[Abbas999]

And I Do not want to copy data from 5 columns. JUst from 1st column. Coulm A which includes all addresses and I have to copy 250 addresses each time. Thank you

You could assign this macro to a shortcut key combo - it starts from the active cell and assumes you are copying 5 columns of data and that you will manually paste to the appropriate destination.

Sub m()
With ActiveCell
    .Resize(250,5).Copy
    .Offset(250).Select
End With
End Sub

 

[FormR]

Please Guide how to? I never used micros and how to assign a shortcut. Please in details I'm totally a newbie here. Thanks

Press ALT+F11 to open the VBE > click "insert" > "module" and paste the code in the window top right. Press ALT+Q to close the VBE

To assign a shortcut read here:
https://support.office.microsoft.co...7752c9&ui=en-US&rs=en-GB&ad=GB#__toc270927002

And I Do not want to copy data from 5 columns. JUst from 1st column. Coulm A which includes all addresses and I have to copy 250 addresses each time. Thank you

Then amend as below:

Sub m()
With ActiveCell
    .Resize(250).Copy
    .Offset(250).Select
End With
End Sub

 

[Abbas999]

Perfect Solution. This is what i was looking for. I used CTRL+S as Short Key. Thank you @FormR

Press ALT+F11 to open the VBE > click "insert" > "module" and paste the code in the window top right. Press ALT+Q to close the VBE

To assign a shortcut read here:
https://support.office.microsoft.co...7752c9&ui=en-US&rs=en-GB&ad=GB#__toc270927002



Then amend as below:

Sub m()
With ActiveCell
    .Resize(250).Copy
    .Offset(250).Select
End With
End Sub