Macro to sort and delete cells

[Potholes]

Hi All

Attached is my cut down file, in Cell A2 we have a formulae that selects the data I need, I now need a MACRO to sort column A by Low to High,
then delete the ROWS that have ZERO data, as the file stands now it will start with VK1RAN finishing with VK9RRR, these could change if new data is registered.


Peter

[than_gold]

Here you go:

Please Login or Register  to view this content.

[Potholes]

Please Login or Register  to view this content.

The cells I am sorting is A2 to E####### umpteen thousands

[than_gold]

Please Login or Register  to view this content.

Please make this change & check.

[Potholes]

Please Login or Register  to view this content.

[sandy666]

Use Report Post to ask moderator to move this thread to appropriate sub-forum
Use code tags for your code: [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE]

[than_gold]

Here its working fine...i'm using Office 365. Which version is you are checking?

[Potholes]

My code is reporting the error as shown below

Please Login or Register  to view this content.

[AliGW]

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Click on Edit to open your thread, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

[Potholes]

is this what you are after ??

Please Login or Register  to view this content.

[jindon]

Code tag should use [ ], not ( )
[code]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/code]

[Potholes]

is this what you are after ??

Please Login or Register  to view this content.

[jindon]

Correct and please edit all your posts as well.

[jindon]

Thanks for the code tag and here's a simple code.

Please Login or Register  to view this content.

[Potholes]

Hi Jindon

Thank you for that code however when I run it it also deleted my Macro command button

File attached

[jindon]

What kind of button is it?

Can you upload a workbook with the button?

[jindon]

I didn't notice that you have uploaded new workbook...

1) Right click on the shape and asign macro "test"
2) Right click on the shape and Properties tab and choose "Don't move or size with cells"

should solve the problem.

[Potholes]

Hi Jindon

Thank you for that code

My actual data base is 900,000 rows by about 60 columns wide, do you think your code will work ok ???

[jindon]

Should work but take a while...

Due to a formula, better to control Calculation mode off/on. like

Please Login or Register  to view this content.

[Potholes]

Hi again

Please Login or Register  to view this content.

[jindon]

No error...............

[Potholes]

Please Login or Register  to view this content.

[AliGW]

Potholes - it would be useful to add a comment to each post with code so that we know why you have posted it and to give members a clue as to what they are looking for.

[Potholes]

Hi me the pain in the BUTT again.

I tried the macro on my main database which goes from column A through to column BF and 900,000 rows.

The macro stops at with the AutoFilter high lighted in yellow

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[jindon]

Which code are you talking about?

Did you write that code for yourself?

[Potholes]

This is the code I extracted from your file which I downloaded
NO I did not write any code the only change I did was here {Range("A:BF").Sort } to change the column out to BF

My master file is A to BF and is actually 1,200,000 rows

Please Login or Register  to view this content.

[jindon]

That's YOUR FILE with my code added.

Formula in Col.A goes to entire column?

Please Login or Register  to view this content.

[sintek]

So...Move your button to top row...

[jindon]

Garbage deleted.

[Potholes]

Garbage deleted.

Good afternoon

If you are still able to assist me great, sometimes my 80 year old brain takes leave of me.

In your code below you have a line that I am not sure of iy's function.:-

Please Login or Register  to view this content.

My data base is 1,400,000 rows by columns A to BF



Peter

[jindon]

My data base is 1,400,000 rows by columns A to BF

Total number of rows in a column = 1,048,576

Simply IMPOSSIBLE.

[Potholes]

Hi

That explains why the error message when opening the original .CSV which I download from the Australian Govt website, Soooooo I am missing a lot potential data..

I checked my data file and yes it is 1,048,576

The things we learn every day Ehhhh

Bugger..

[jindon]

Only the way is to read the data directly from CSV(Text file) and manipulate within memory.

[Potholes]

Hi all

I am NOT starting another post, just putting up where I am up to ATM, hoping to catch Jindon..

I have a .csv data base that has 1,400,000 rows and 55 columns. Using a text editor I have opened the csv file and copied
about 50% to a Excel sheet called Data_1 and the remainder off to Data_2. I have attached a file called Book_2
which is the smallest file allowable to upload.

I had guru (jindon) help me with some code for a Macro which is below,:-

Please Login or Register  to view this content.

What I need is the macro to sort column A ( to only find data that is VKx
(any number between 0 to 9) and the 3 following letters starting with R and only like eg:-
VK4RLW VK1RIG VK3RTT etc, it needs to ignore eg:- VK5RR VK1RZ etc, at the moment when I run
the macro on each sheet it only sorts column A from Lowest to highest, and showing the data
I need, atm it deletes the header row which I need to keep.

Any help would be appreciated..

Peter

[AliGW]

You have started (and finished) another POST, but not another THREAD - thank you for not starting another thread.

[Potholes]

Hi Have done right or wrong please. ???

[AliGW]

Thank you = right.

Post = individual message within a thread.
Thread = chain of posts (messages) within a forum query.

[jindon]

In order to read diretly from csv, I need to see csv (text file).

I'm not with mt pc, so if upload the file, I can write a code when I come back.

Sending this from phone.

[Potholes]

Hi

The .CSV file is 200.4 meg so I cannot upload here.

Pete

[jindon]

No that much.
Just about first 20 - 30 lines shoild be Ok, incding header if it has.

[Potholes]

The book_2.xlsm file that I have uploaded has the header and 13,000 + rows, contains the 55 columns, with the header.

[jindon]

Ok, I'll see when I come back.

[Potholes]

Good evening

I have attached the current file with ALL of the columns, some were missing off the previous file from today.

Peter

[jindon]

Tested and extracted 155 records based on your book, so test this with your actual csv.
If extracted rows still exceed the number of rows in Excel, it will output as CSV in the same folder.

Please Login or Register  to view this content.

[Potholes]

Hi I can't open the whole CSV file in excel as it is too big and I am not sure how to use your macro in conjunction with the csv file

[jindon]

Just select the original csv file.

CSV file and Excel workbook is totally different.
Excel CAN open csv, but csv file is a comma delimited TEXT file.

You know what I mean?

If you right click on csv file and open with notepad, you will see actual contents.

[jindon]

Book2.csv is the file converted and saved as csv from Book2.xlsm you uploaded.
So, select above file when the dialog comes up.

[Potholes]

The file I uploaded to you is HALF of the full CSV file, the
Data_1 page has 1,008,654 rows 55 columns
Data_2 page has 16,673 rows 55 columns it is this one I uploaded known as Book_2.xlsm

So I am unsure where to go from here.

[jindon]

You are talking bout Excel workbook.

CSV file can not have more than 1 page...

OK, tell me where original csv file which has 1,400,000 rows come from?
Do you have original csv file?

[jindon]

After several PM exchange, now OP's request is clear.
Get data from CSV where value in CALL_SIGN column is like VK#R & 2 alphabets or more.
Move CALL_SIGN column to the first and sort by CALL_SIGN.
Create a new csv file in the same folder.

Please Login or Register  to view this content.