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

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
Tim,

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

Jodie
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,186
Members
448,554
Latest member
Gleisner2

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
Back
Top