none
Copying a range, deleting the cells which have nothing in them (or 0 in them) transposing the range and pasting into another sheet RRS feed

  • Question

  • Hi guys,

    I know what to do but not entirely sure how to do it:

    1) Copy range (single column) and paste into another sheet

    2)Transpose

    3) Delete cells which contain 0 - naturally will involve IF block

    Sub UpdateKey()
        With Range("P10:P36")
            .Value = .Value
            .RemoveDuplicates Columns:=1, Header:=xlNo
            On Error Resume Next
            .SpecialCells(xlCellTypeBlanks).Delete xlShiftUp
            On Error GoTo 0
        End With
    End Sub

    Found this code here

    However I don't understand the On error resume bit, what does that do?

    Also do you guys think this is an efficient way of doing it?

    Many thanks


    • Edited by VBNovice01 Thursday, January 5, 2017 1:36 PM
    Thursday, January 5, 2017 11:52 AM

Answers

  • Hi,

    I suppose you want to X-Y convert (Column-Row).
    Please try to:
      1. select [Record Macro]
      2. copy A1:A10
      3. select A2
      4. [past] - [Paste Options] - Transpose
      5. [Stop Recording]

    and examine a created module.

    You will see like this:

    Sub Macro1()
        Range("A1:J1").Select
        Selection.Copy
        Range("A2").Select
        Selection.PasteSpecial _
            Paste:=xlPasteAll, _
            Operation:=xlNone, _
            SkipBlanks:= _
            False, _
            Transpose:=True
    End Sub


    It's your solution. 
    It would be better to remove unnecessary lines (make it simple).

    Regards,
    Ashidacchi


    • Edited by Ashidacchi Friday, January 6, 2017 9:57 AM
    • Marked as answer by VBNovice01 Friday, January 6, 2017 11:48 AM
    Friday, January 6, 2017 9:52 AM

All replies

  • Hi,

    Please refer to:
    Error Handling In VBA
    http://www.cpearson.com/excel/errorhandling.htm

    I've tried without "On Error", and found no problems.
    But I think it's better to use "On Error", in preparation for unexpected values involved and for error code. 

    Sub UpdateKey()
        Worksheets("Sheet2").Range("P10:P36").Value = ""
        Worksheets("Sheet1").Range("P10:P36").Copy Worksheets("Sheet2").Range("P10")
        ' ---remove Duplicate
        With Worksheets("Sheet2").Range("P10:P36")
            .Value = .Value
            .RemoveDuplicates Columns:=1, Header:=xlNo
            ' On Error Resume Next
            .SpecialCells(xlCellTypeBlanks).Delete xlShiftUp
            ' On Error GoTo 0
        End With
        ' --- remove Zero
        With Worksheets("Sheet2")
            Dim i As Integer
            For i = 10 To 36
                If (.Cells(i, 16).Value = 0) Then  ' -- P=16
                    .Rows(i).Delete Shift:=xlUp
                End If
            Next
            .Select
        End With
    End Sub

    Regards,
    Ashidacchi
    Friday, January 6, 2017 1:55 AM
  • Many thanks for your reply Ashidacci. That certainly clears things up, it looks like a better version of what solution i came up with. 

    Sub reorg()
        Dim i As Long, j As Long, k As Long
        Dim N As Long
        N = Cells(Rows.Count, "A").End(xlUp).Row
        j = 2
        k = 1
    
        Range("H1").Select
        Range(Selection, Selection.End(xlDown)).Select
        Selection.Copy
        Sheets("Sheet2").Select
        Range("A1").Select
        ActiveSheet.Paste
        
        Dim rng As Range
        Dim counter As Integer
    
        'Set the range to evaluate to rng.
        Set rng = Range("A1:A56")
    
        'initialize i to 1
        i = 1
    
       'Loop for a count of 1 to the number of rows
        'in the range that you want to evaluate.
        For counter = 1 To rng.Rows.Count
    
            'If cell i in the range contains an "o",
            'delete the row.
            'Else increment i
            If rng.Cells(i) = "o" Then
                rng.Cells(i).Cells.Delete
            Else
                i = i + 1
            End If
    
        Next
        
    End Sub
    

    However, can't seem to get the transpose right. I'm trying to transpose a single column to a single row so for example A1:A10 would be transposed to A1:J1. Seems simple but Ikeep getting an incorrect result. I've tried pressing the transpose button too

    Friday, January 6, 2017 9:25 AM
  • Hi,

    I suppose you want to X-Y convert (Column-Row).
    Please try to:
      1. select [Record Macro]
      2. copy A1:A10
      3. select A2
      4. [past] - [Paste Options] - Transpose
      5. [Stop Recording]

    and examine a created module.

    You will see like this:

    Sub Macro1()
        Range("A1:J1").Select
        Selection.Copy
        Range("A2").Select
        Selection.PasteSpecial _
            Paste:=xlPasteAll, _
            Operation:=xlNone, _
            SkipBlanks:= _
            False, _
            Transpose:=True
    End Sub


    It's your solution. 
    It would be better to remove unnecessary lines (make it simple).

    Regards,
    Ashidacchi


    • Edited by Ashidacchi Friday, January 6, 2017 9:57 AM
    • Marked as answer by VBNovice01 Friday, January 6, 2017 11:48 AM
    Friday, January 6, 2017 9:52 AM
  • Thank you again for you help Ashidacci, that's cleared everything up.

    Kind Regards

    Friday, January 6, 2017 11:48 AM