List on new sheet based on if there are values

jobee

New Member
Joined
Nov 21, 2004
Messages
7
Hi everyone,

Just wondering if anyone can give me a hand with a list I am working on, basically I have a list of products however there are rows which contain no data. What I want to do is move this into a consecutive list on another sheet without the blank rows.

For instance there is a product in A6 but then there aren't any products until cell A12, so on my second sheet I want the products for these to list in cells A3 and A4.

This is a dynamic list and will change often.

Thanks everyone. :rolleyes:
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

tmcfadden

Board Regular
Joined
Aug 17, 2005
Messages
151
Here's something that might work for you...

Select all of the rows you want to copy ( including the blank ones ).

Select Data->Filter->AutoFilter from the menu.

Press the filter button ( above the first cell of data ). From the drop-down, select "Nonblanks".

From there, you can select the all of the data like normal ( perhaps select the first cell and then press CTRL+SHIFT+DOWN, then CTRL+C ).

You can paste the series without any blanks.

-Tim
 

jobee

New Member
Joined
Nov 21, 2004
Messages
7
Thanks Tim but I need something more automated and idiot proof. This is actually for work and I have to make it as automated as possible so that human error is minimal. Thanks anyway though.
 

tmcfadden

Board Regular
Joined
Aug 17, 2005
Messages
151
Ah yes... the ugly business of automation.

Try this code:
Code:
Sub CopyWithoutBlanks(rngSource As Range, rngDestinationCell As Range)

Dim iCount As Integer

iCount = 0
For Each cell In rngSource
    If (cell.Value <> "") Then
        rngDestinationCell.Offset(iCount).Value = cell.Value
        iCount = iCount + 1
    End If
Next cell


End Sub

Use it like: CopyWithoutBlanks(Sheet1.Range("A1:A50"),Sheet1.Range("B1"))


-Tim
 

jobee

New Member
Joined
Nov 21, 2004
Messages
7

ADVERTISEMENT

Tim,

I tried this but it keeps showing compile errors. I might just be being a little daft. Sorry

Jodie
 

agihcam

Well-known Member
Joined
Jan 16, 2006
Messages
1,624
Hi-
assuming the list in columnA
Code:
Sub test()
Dim i As Long
For i = 2 To Range("a" & Rows.Count).End(xlUp).Row
    If Cells(i, "a").Value > 0 Then
        Sheets(2).Range("a" & Rows.Count).End(xlUp).Offset(1) = Cells(i, "a").Value
    End If
Next
End Sub
 

tmcfadden

Board Regular
Joined
Aug 17, 2005
Messages
151

ADVERTISEMENT

Tim,

I tried this but it keeps showing compile errors. I might just be being a little daft. Sorry

Jodie

Hmm, it works on mine.

What are the exact errors your getting? Perhaps it's a difference in Excel verion.

-Tim
 

jobee

New Member
Joined
Nov 21, 2004
Messages
7
Compile Error

I'm using 2003, if that makes any difference. It's been ages since I've used modules and I feel like I'm going around in circles.

:oops: I feel like a bit of a boofhead but can you possibly give me some more details on how to make this work.

The exact details are - I'm trying to copy cells A4:A17 on sheet Cable_1 to cells C13:C23 on sheet Parts_List_1.

I'm probably being rather blonde but I really really appreciate the assistance.

Regards
Jodie
 

Forum statistics

Threads
1,136,354
Messages
5,675,301
Members
419,560
Latest member
g3org

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top