Question on copy/pasting dynamic information

[brunette]

Hi everyone,

I have a sheet where I have information that is copied over from an external source in blocks. By this I mean:

In column A I will have the title of a location;
in Col B I will have various lines of specs; Column C I will have the Country of the location in column A etc.

Looks something like this (....... represents blank cells):

LOCATION1 Spec1 Country1
.................Spec2............
.................Spec3............
LOCATION2 Spec1 Country1
.................Spec2............
.................Spec3............

This repeats for numerous but varying locations every month.

So I want it to look like this as I need to sort, count locations etc:

LOCATION1 Spec1 Country1
LOCATION1 Spec2 Country1
LOCATION1 Spec3 Country1
LOCATION2 Spec1 Country2
LOCATION2 Spec2 Country2
LOCATION2 Spec3 Country2

My question is, is it possible to automate the copying of each location and country info in the blank cells with this all being dynamic?

Thanks again as always,
Ana

 

[steve the fish]

You could try this:

Highlight your data. Press CTRL-G. Press the Special button. Press the blanks radio button. Then OK. Press = then the up arrow key. Press CTRL-ENTER. Copy and paste special values if required.

 

[brunette]

I forgot to mention that the information in column B (specs) is also varying. Some months may have more or less entries.

So, Column A = variable data
Column B = variable data
Column C = variable data

Hope this helps!
Ana

 

[brunette]

You could try this:

Highlight your data. Press CTRL-G. Press the Special button. Press the blanks radio button. Then OK. Press = then the up arrow key. Press CTRL-ENTER. Copy and paste special values if required.

Holy Moly that worked! And I learnt something new that isn't VBA related! Thank you!

 

[brunette]

Hi again,

Now I've run into another issue...

If I try to automate this step by using the following code to select all my (variable) data and apply the above steps, it tells me I have no blank cells. If, however, I select all the (same) data manually, it works.

Any idea how I can incorporate this into a working macro?

Code I have used (that is causing error)

Sheets("Sheet1").Select
    Dim LR As Long
    LR = Range("A" & Rows.Count).End(xlUp).Row
    Range("A2:E" & LR).SpecialCells(xlCellTypeConstants, 23).Select
    
    Selection.SpecialCells(xlCellTypeBlanks).Select
    Selection.FormulaR1C1 = "=R[-1]C"

EDIT:

Never mind. I figured out my mistake.

Fixed code:

Sheets("Sheet1").Select
    Dim LR As Long
    LR = Range("A" & Rows.Count).End(xlUp).Row
    Range("A2:E" & LR).Select
    
    Selection.SpecialCells(xlCellTypeBlanks).Select
    Selection.FormulaR1C1 = "=R[-1]C"

 

[steve the fish]

When using LR there based on your data you should probably use column B as column A has blanks...so maybe:

LR = Range("B" & Rows.Count).End(xlUp).Row
Range("A2:E" & LR).SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"

 

[brunette]

Hi Steve,

As always, you're my lifesaver. I will give that a try since I notice that with my current code it doesn't pick up the data from the last group, so definitely going to give it a whirl. Thanks again!