Home » questions » How to select all the rows in excel that has a atleast 1 value in it.?

How to select all the rows in excel that has a atleast 1 value in it.?

4
2006-08-11 07:06:03, Category: Programming & Design
I want 2 select all the rows in excel that has atleast 1 key entry in it. There are no blank rows in between. I want to achieve this in a excel macro. Can anyone help me please? Thank you. I am adding a bit more info. We are actually creating multiple spreadsheets in a macro which is then uploaded into mainframe datasets and then updated to a database thru a job. If we have any blank lines in the spreadsheet in the end, the load job will abend. These spreadsheets are actually created using a XL template which again has blank lines at the end. I mean blank lines like when u press "CTRL+ SHIFT+END", if the rows selected has some blank rows, then they are also copied into the new spreadsheets created by the macro which causes the mainframe job to abend. All I wanted here is if I do "CTRL+SHIFT+END", it should only the rows which has atleast 1 valid value. How can I do that. Please let me know if you need more info. Thank you.

Answers

  1. Lewiy

    On 2006-08-13 11:51:28


    Using the example that you rows which contain data in one of 5 columns (A to E). In cell F1 enter the formula: =count(A1:E1) This will tell you the number of non-blank cells in row 1. Then copy this formula down for every row. Select the whole of column F and click on Data -> Filter -> Auto Filter. You will see a little drop down arrow at the top of column F. Click this arrow and select "Custom". In the new dialogue box that appears change the options to say "Does not equal" and "0". Excel now filters your spread sheet to show all of the rows which have at least 1 non-blank cell in them. Job done.

  2. Dilip Rao

    On 2006-08-13 09:03:10


    Study the help section on Filter (Auto Filter). I think thats what you are looking for.

  3. jlcaooscl

    On 2006-08-11 07:12:44


    press shift and while keeping it pressed hit end and arrow down. alternatively, hover your mouse over the little square on the bottom right of the active cell (as long as the active cell is the first cell on the column you need) and double click on it.

  4. XLMan

    On 2006-08-12 07:03:00


    I need more info You want to select the entire row if one value in it? Or you want to select the entire row if there is no blank cells in it? These are differant scenarios. Why you want to use macro when you can use Edit > Go to with the INDIRECT + ADDRESS + IF + ... functions? Read my profile, I am XLMan