Pasting macro for skipping blank cells


Board Regular
Nov 28, 2007
Hi everyone. I'm having a bit of trouble with a macro I'm using to select certain info.
I don't know much about VBA but I copied a couple old macros I previously had and I've modified them to try and suit the application. It didn't work for me though. (failed miserably actually)

I'm doing up a customer order form for work & its based on our product's price list.
I already have the price list set up with a check box system. Our sales people can check the box for the item a customer wants and then they can select the customized items for the machine etc...
Our sales people want to be able to automatically print off the order form for our clients to sign off with all their selected items without the customer seeing the rest of the price list.
I'm hoping to put automatic borders on it and try and make it look as professional as possible.

I have the basic product information in range p6:r10 (The "P" cells are the description & "R" is it's price). Only one of these rows should be selected by the customer.
I've set up the check box so that when an item is picked the corresponding "P" cell is then visible IE P6<>"" or p7<>"" or P8<>"" etc.

Dim lngRow As Long, rng As Range
If Range("p6") <> "" Then
For Each rng In Range("q6:q10")
If rng.Value <> "" Then lngRow = rng.Row
Next rng
Range("q6:r" & lngRow).Copy
If Range("v6") = "" Then
Range("v6").PasteSpecial xlPasteValues
Cells(Rows.Count, "v").End(xlUp).Offset(1).PasteSpecial xlPasteValues
End If
MsgBox "Input data is blank - no copy performed!": Exit Sub
End If

Once this is done i wanted to copy the value from cell Q11 and paste it directly after the above info. I wanted to then select this pasted cell along with the cell beside it "W" and make both of them grey with a standard border around the two cells. (This is like a title for some of the customized components to follow)

Here's the tricky bit.
I want to now select all the visible cells from range Q12:R24 and paste it after the title from Q11. This is similar to the first bit. if the p cell is blank then it is to be ignored.
But these cells can be visible in any order whatsoever as multiple items can be selected.
I was hoping to put a border around these cells too but individually on the item description and its cost on the right.

The order form continues like this with more categories but if someone could help me with the above bit I think I could take it from there by changing the cell names.

If anyone can help me with this, I'd be very grateful. Unfortunately I don't know enough (anything) in VBA to be able to do it myself.

Forum statistics

Latest member

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...