+ Reply to Thread
Results 1 to 4 of 4

How to Transform Blocks from Horizontal Data to Vertical Data (example attached)

  1. #1
    Registered User
    Join Date
    12-09-2018
    Location
    Tasmania, Australia
    MS-Off Ver
    2013
    Posts
    2

    How to Transform Blocks from Horizontal Data to Vertical Data (example attached)

    Hi All,
    It's been a while since I needed to use VBA.. I'm embarrassed as this may be really simple but I've tried and failed so far...
    In the example attached I need to change format from what's on Sheet1 to what's on Sheet2.
    I'm working with a font and how it is displayed using a Microcontroller (PIC), so there's 256 records * 9 columns by 9 rows (I need 1 row space between)
    I tried OFFSET as it seemed obvious, also INDEX and TRANSPOSE (but it mirrors).. no good, also found a great bit of code for rotate (8*8) but couldn't alter it to repeat...
    anyone help?
    ** this all seemed so easy when I was younger, now at 64 the synapses are just slower..
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    20,153

    Re: How to Transform Blocks from Horizontal Data to Vertical Data (example attached)

    Do you mean?
    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    12-09-2018
    Location
    Tasmania, Australia
    MS-Off Ver
    2013
    Posts
    2

    Re: How to Transform Blocks from Horizontal Data to Vertical Data (example attached)

    Thanks jindon... that works for me. Can you expand on how this works?
    I can understand the "SpecialCells" but am struggling with the "IIf" part.

  4. #4
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    20,153

    Re: How to Transform Blocks from Horizontal Data to Vertical Data (example attached)

    IIf function in vba works like If function in worksheet.

    Returns one of two, IIf(Condition, When true, When false)
    Could be rewrite like
    Please Login or Register  to view this content.
    So, when loop counter = 1, it returns 1, otherwise 3.
    It is used for the row to be pasted in sheet2.
    End(xlUp) refers to the last non empty cell, but when the column is blank, it returns 1.
    End(xlUp)(1) actually means End(xlUp).Cells(1, 1) (RowRef, ColRef from the cell itself and both optional, 1, 1 is the default).
    End(xlUp)(3) will give you 2 rows below after the last non empty cell, so it gives you 1 blank between the data.

    One thing you should be very carefull using IIf function is, unlike If in worksheet function, it evaluates both True, False completely.

    Therefore you can not used it e.g IIf(Range Is Nothing, Empty, Range.Value), because if Range is Nothing Range.Value is impossible.

    HTH.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 8
    Last Post: 10-11-2018, 01:13 PM
  2. Formula for transform data horizontal.
    By inform in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-09-2017, 02:53 PM
  3. Help on Ideas of how to transform data from a vertical to more horizontal layout
    By welchs101 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-29-2016, 03:02 AM
  4. Transposing repeating vertical data to horizontal data but retaining unique values only
    By mgcarino14 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-06-2014, 12:57 AM
  5. Replies: 12
    Last Post: 01-07-2014, 05:08 PM
  6. Transpose horizontal data to vertical data with paste link
    By M.Devadhasan in forum Excel General
    Replies: 0
    Last Post: 07-07-2012, 01:01 PM
  7. Replies: 5
    Last Post: 04-23-2010, 08:01 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1