Excel - Fill empty cells

[Yendorian]

I have an EXCEL file which consists of two worksheets (1 & 2).
Data input into WS1 is transferred (by means of macro) and transposed in WS 2.
e.g. Sheet 1 entries
D E
11 Name Smith
12 First name Michael
13 Country UK
14 Age 26
and a new record
D E
11 Name Brown
12 First name Susan
13 Country
14 Age 21

become

Sheet 2
A B C
D
1 Smith Michael UK 26
2 Brown Susan 21

However, if a cell is left empty on Sheet 1 (E13 above), the info from the
next cell in that column automatically moves into the empty cell (C2 on sheet
2).

Assing
D E
11 Name Jones
12 First name Richard
13 Country Germany
14 Age 28

gives

Sheet 2
A B C
D
1 Smith Michael UK 26
2 Brown Susan Germany 21
3 Jones Richard 28

I either need to put a 'space' as a place holder in the empty cell (Sheet 1
E13) thus making it think there is something in that cell or format Sheet 2
so that any 'empty' cells are filled with a 'blank' placeholder.
Hope this is clear and that someone can help.
Thanks for a brilliant forum
RG

 

[Dave Thomas]

I used the TRANSPOSE function and got the following results
A B C D
1 Smith Michael UK 26
2 Brown Susan 21
3 Jones Richard Germany 28


I have no idea what you're doing in your macro.

 

[Dave Thomas]

Nothing like having the software remove my spaces
I used the TRANSPOSE function and got the following results


A1 = Smith, B1 = Michael, C1 =UK, D1 =26
A2 = Brown, B2 = Susan, C2 = Empty cell, D2 =21
A3 = Jones, B3 =Richard, C3 = Germany, D3 = 28

I have no idea what you're doing in your macro.

 

[Yendorian]

Hi Dave,
Thanks for the reply.
I can do the transfer manually using the TRANSPOSE option - no problem. what
I want id for one person to enter the data on sheet 1 and have nothing to do
with sheet 2 where I do the calcualtions etc.

This is the code i am using to transfer the records from sheet 1 to sheet 2
Sub test()
Application.ScreenUpdating = False


Sheets(1).Range("e6").Copy
Sheets(2).Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial

This is asiple code offered by another contributor to the forum.
Rodney