How can i mark empty cells who is not contains any data

Morty07

New Member
Joined
Oct 15, 2014
Messages
20
Hi, I have an excel sheet where i want to put a mark on. After that i will extract those data on the line to a new sheet. It is difficult to explain but take å look at the file.

The number is å productnumber, second is description, third is the product price, the fourth is the number how many they bought, the fifth is total price.
Between the different product description is an total line. The product column are yellow. Do anyone have any idea or process to get it out like this:

100007 - Hvit linjemaling - total product price - total numbers - total price

I do think i have to do this in several processes, but I'am looking for an easy way to those processes.


100007
100007
100321
100572
100718
100718

<colgroup><col></colgroup><tbody>
</tbody>
Hvit linjemaling - Oppmerking gressbane
Hvit linjemaling - Oppmerking gressbane
Hjørnestolpe, Fleksibel
Hjørneflagg - Rødt
Wire i stål, 6mm
Wire i stål, 6mm

<colgroup><col></colgroup><tbody>
</tbody>
Stk
Stk
Stk
Stk
Meter
Meter

<colgroup><col></colgroup><tbody>
</tbody>
453,14
795,00
585,64
100,92
12,61
65,52
8,19
8,93
8,93
9,74

<colgroup><col></colgroup><tbody>
</tbody>
3,00
3,00
6,00
8,00
8,00
8,00
8,00
6,00
5,00
11,00

<colgroup><col></colgroup><tbody>
</tbody>
1 359,42
2 385,00
3 744,42
807,33
807,33
524,14
524,14
53,56
44,64
98,20

<colgroup><col></colgroup><tbody>
</tbody>

<tbody>
</tbody>

I'am living in a hope that someone can help me with this.

Morty
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
Re: How can i mark empty celles who is not contains any data

Not saying I'll answer this but it's not clear from your diagram what data is in which cell.
At the moment it looks like your diagram consists of 8 columns by 2 rows and the Product Numbers are all in ONE cell (A1 presumably).
If this is not the case you need to edit your diagram to clarify what data is on what row and in which cell.
 
Upvote 0
Re: How can i mark empty celles who is not contains any data

Ok thank you I will do that.
A = Productnumber B= productdescription C= Size etc D= Product price E=how many customer have ordered F=Total price
 
Upvote 0
Re: How can i mark empty celles who is not contains any data

How about
Code:
Sub Morty07()
   Dim Rng As Range
   Set Rng = Range("A:A").SpecialCells(xlBlanks)
   With Range("A2", Range("D" & Rows.Count).End(xlUp).Offset(, -1))
      .SpecialCells(xlBlanks).FormulaR1C1 = "=r[-1]c"
      .Value = .Value
   End With
   Rng.EntireRow.Copy Sheets("Tmp").Range("A2")
End Sub
 
Upvote 0
Re: How can i mark empty celles who is not contains any data

Ehh Fluff,

I do not have a clue what this means, and where do i activate this. Morty

How about
Code:
Sub Morty07()
   Dim Rng As Range
   Set Rng = Range("A:A").SpecialCells(xlBlanks)
   With Range("A2", Range("D" & Rows.Count).End(xlUp).Offset(, -1))
      .SpecialCells(xlBlanks).FormulaR1C1 = "=r[-1]c"
      .Value = .Value
   End With
   Rng.EntireRow.Copy Sheets("Tmp").Range("A2")
End Sub
 
Upvote 0
Re: How can i mark empty celles who is not contains any data

Dear Fluff, sorry about delyed answer. You are my hero. Thank you so much, it helped me. Now I'am gonna learn macro when og got time. Can i give you anu cudos or something.

Morty
 
Upvote 0
Re: How can i mark empty celles who is not contains any data

You're welcome & thanks for the feedback.

Letting me know that it works & a word of thanks is all I need :)
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,916
Members
448,533
Latest member
thietbibeboiwasaco

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