Posts by apo

    HI.. Here's another one to try..

    It would be interesting to see which one is faster.. probably KJ's as although there is an extra loop.. it's all array based.


    Here's something you can adapt... it just puts a border around every cell containing a constant on the Sheet change Event.

    So if are entering anything other than a constant (likeFormulas).. then this one is no good. (Paste it into your sheet code)

    Private Sub Worksheet_Change(ByVal Target As Range)
     With Cells.SpecialCells(2)
      .BorderAround ColorIndex:=1, Weight:=xlThin
      .Borders(xlInsideHorizontal).LineStyle = xlContinuous
      .Borders(xlInsideVertical).LineStyle = xlContinuous
     End With
    End Sub

    Another option would be to use the Range.Areas Property and loop through each Area in your used range and apply a border to the whole area similarly to how it is applied above.

    Depending on how much data you have.. you could either put it again on the sheet change event or have a button to press that applies it at the users will.

    Hi.. late to the party.. but just for fun.. maybe something like this too could work..

    Private Sub CommandButton1_Click()
        Dim x, lr As Long, i As Long
        x = Sheets("input").Cells(1).CurrentRegion.Resize(, Sheets("input").Cells(1).CurrentRegion.Columns.Count + 1)
        For i = 1 To UBound(x, 2) - 10
            lr = Sheets("Test").Range("A" & Rows.Count).End(xlUp).Row + 1
            Sheets("Test").Cells(lr, 1).Resize(UBound(x) - 1, 11) = Application.Index(x, Evaluate("row(2:" & UBound(x) & ")"), Array(1, 2, 3, 4, 5, 6, 7, 8, 9, UBound(x, 2), 9 + i))
            Sheets("Test").Cells(lr, 10).Resize(UBound(x) - 1, 1) = Application.Transpose(x(1, 9 + i))
        Next i
    End Sub

    Here's another possibility, if your Workbook remains the same format as the one you uploaded..

    Hi.. Welcome to Ozgrid..

    You didn't say what happens to column C.. so here's 2 versions..

    Version 1: Column C becomes the last column (column F).

    Private Sub CommandButton1_Click()
    Dim x
    With Range("A1:F" & Range("A" & Rows.Count).End(xlUp).Row)
       x = .Value
       .Value = Application.Index(x, Evaluate("row(1:" & UBound(x) & ")"), Array(6, 4, 5, 2, 1, 3))
    End With
    End Sub

    Version 2: Column C is not wanted... which brings in the need to clear the existing range and set the size of the range that the values will be dumped into.

    Private Sub CommandButton2_Click()
    Dim x
    With Range("A1:F" & Range("A" & Rows.Count).End(xlUp).Row)
       x = .Value
       [A1].Resize(UBound(x), UBound(x, 2) - 1).Value = Application.Index(x, Evaluate("row(1:" & UBound(x) & ")"), Array(6, 4, 5, 2, 1))
    End With
    End Sub

    More Info:


    Here's another that works on your sample (although you MUST remove the text "End" that was in row 15000 (the black row) on each of you DBEntry sheets.. not sure why that was there.. and it makes it hard to find the last used row of data..

    The idea below is to read each Workbook once, add data to Collection and build array, then dump array to sheet.. so as little sheet interactions as possible.

    I haven't played with Collections much at all, but found Pikes thread on it valuable..…excel-vba-collection-sort

    It seems you can't dump a Collection straight to sheet( like you would an array)so that's why the extra step of building the array from the Collection is needed..

    Naughty cross poster.. ;/

    Not sure why you would just want to select those cells.. if your going to do anything with them.. selecting them is probably the least efficient way of including them..

    Any way.. there is MOST probably a better regex pattern.. but this seems to work..

    Just a thought.. maybe add a "Search" textbox so you can get to the wanted item faster..

    You could set the z variable to be a range (column 1 in the "Database" sheet).. or make it a Public variable and assign Pikes array to it..

    I have attached a Workbook showing kinda what i mean.. enter some text in the "Enter Item" textbox to populate the combobox.. also.. pressing the Tab key while in the textbox will populate the combo with all values (so you can scroll I guess). double click in combo shows selected item.

    The way I populated the z array is a bit dodgy though.. I used Scripting Dictionary to find uniques then fed it into an arraylist and sorted... Pikes way it better.

    btw.. here is some good stuff right here:…ctions-arraylist?t=167349


    Probably shouldn't Dimensions variable for a lastrow as an INteger.. as it has a max value of 32767.. better to dim it as long.
    Also.. you should specify explicitly the sheet that "Rows.Count" is referring to.... like "....Cells(Sheets("Previous Period DT37").Rows.Count,1)......."


    Here's another.. click the blue button to test,

    I am not sure what you want to do in the "Do stuff here" part.. but you could also add validation that the user has opened the correct file etc before executing any other code..


    Bit rusty.. but maybe something like this?

    Click the blue button on sheet1 to test..

    Edit: I didn't see the part about inserting "No Match".. added in red.. add that to the sample workbook to test..


    You should really create a new thread.. it helps when others are searching for the same solution..

    Your code wasn't getting all the data you wanted because you were using xlDown instead of Xlup..

    In any case.. try this:

    Sub Copy_Columns()
    Dim x
     x = Sheets("HomeWork").Range("A4:H" & Sheets("HomeWork").Range("A" & Rows.Count).End(xlUp).Row)
     Sheets("NavInv").[C2].Resize(UBound(x, 1), 5).Value = Application.Index(x, Evaluate("row(1:" & UBound(x) & ")"), Array(1, 3, 4, 2, 5))
    End Sub

    btw.. Also post your code within the code tags.. it's the little hash symbol on the edit bar..

    Re: Excel Vba to transpose single row to multiple columns

    Here's another...

    Re: uppper, proper case & font/size

    Something like..

    Re: New Macro or Code to Move Rows to Another Sheet Based on Cell Value

    HI.. Try this.

    Re: Importing multiple text files with names into excel (different rows)

    Hi.. welcome to Ozgrid.

    If all your txt files have the same format.. maybe this also..

    Re: Macro Code to delete specific data in specific columns


    Another way would be to use Autofilter.. no loop needed..

    Private Sub CommandButton1_Click()
        Application.ScreenUpdating = False
        With [A1].CurrentRegion
            .AutoFilter 3, "Africa"
            .AutoFilter 4, "Information"
            .Parent.AutoFilter.Range.Rows.Offset(1).Resize(.Parent.AutoFilter.Range.Rows.Count - 1).SpecialCells(12).EntireRow.Delete
        End With
        Application.ScreenUpdating = True
    End Sub

    Also.. London.. take a look at the Union method.. it allows you to build a range as you loop and then do something with that range at the end (like delete or copy etc).. it will be faster as there is less sheet interaction..

    Re: Import codes from once sheet to the other


    Please put code tags around any code you post. (When in the Reply box.. highlight your code and click the little # icon in the Reply box menu bar). I have done it for you in your last post.

    Sub ImportProdCode()
        Dim x, i As Long, cnt As Long
        x = Application.Transpose(Sheets("SubstoreBuyPlan").Range("A2:A" & Sheets("SubstoreBuyPlan").Range("A" & Rows.Count).End(xlUp).Row))
        cnt = 1
        For i = LBound(x) To UBound(x)
            Sheets("SubstoreProductMaster").Cells(i + cnt, 1).Value = x(i)
            cnt = cnt + 24
        Next i
    End Sub

    You had a couple of issues when you adapted the code i gave you.

    1. The way you are referencing your Worksheets would require you to Declare and Set them first. I may have confused you in that the way I referenced them in the initial code was by the sheets index number. IN the second code I show how to reference it by sheet name (one way)..

    2. Your missing the part where you should turn screenupdating to False at the start of your code.

    3. Not that I think you need to do this.. but you are setting the calculation mode to automatic at the end but not setting it to manual at the start.. so it is pointless.

    Lemme know how you go.