Rtyjn

Excel VBA to stack columns in different sheets I have an Excel workbook with 2 sheets inside. Both sheets have tables that can change in length (row count). I want to be able to have VBA take table1 in sheet1 and align it within table2 on sheet2. Here's where it gets tricky. The columns do not line up. For example, "Company" in sheet1 might be in column E. However, in sheet2 it's in column V. There are also blanks in the columns. Which is ok as far as data goes, but it's going to make getting to the end of the table's column harder. I know I'm going to need to list out which columns go where for each column. In sheet1 there are 36 columns. In sheet 2 there are 49. This is perfectly acceptable. Any guidance would be greatly appreciated. It takes an insane amount of time to do this manually and I'm just trying to speed it up. If it's easier to get everything to compile on sheet3, that's fine. Here is what I have so far, but it's not liking th...

copy data according to the length of the adjucent column Columns("Q:Q").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Dim lastRow As Long lastRow = Range("O3" & Rows.Count).End(xlUp).Row Range("P4:P" & lastRow).Select Selection.Copy Range("Q4").Select ActiveSheet.Paste I am trying to insert a column next to Q column, copy the data from P column based on the length of the adjacent column ("O") and paste the data into the inserted column. Above is the code I am using to achieve this. But the problem is column P and O don't have the same length of data every time. I am not sure what I am missing here. Someone correct me where I am doing it wrong. Thanks in Advance! 1 Answer 1 Try this ("O3" & Rows.Count caused problems). Also no need to select cells. Columns("Q:Q").Insert Shift:=xlToRight, CopyOrigin:=x...

VBA Count occurrence of an element in a multi-dimensional array, how? I have defined a multi-dimensional array using range in vba, for example Dim Arr() as Variant Arr = Range("A1:F5") This resulted the a 5x6 array Arr(1,1) to Arr(5,6) I want to count the occurrence of a string, say "ABC" in Arr(5) (i.e. Row 5) only. The following code can find the count of "ABC" in the all of the array For Each x in Arr if x = "ABC" then Cnt = Cnt + 1 Next But if only want to count dimension 5, Arr(5) return an error. Arr is Arr(1 to 5, 1 to 6). Which 5 are you talking about? – Jeeped Jun 30 at 9:22 2 Answers 2 Sub FindABC() Dim Arr(), cnt, x Const ROW_NUM = 5 Arr = Range("A1:F5") For x = 1 To UBound(A...

Is there a faster CountIF As the title says. Is there any function or VBA code which does the same function as a countif and is a lot faster. Currently in the middle of massive countif and it is just eating up my CPU. It is just a basic countif inside the worksheet. Not in VBA. =countif(X:X,Y) However the lists are massive. So both lists are around 100,000~ rows =countif(X:X,Y) Not sure if it's faster or not, but you could try filtering the column on the IF part, then get Range.SpecialCells(xlVisible).Count . Note: not 100% certain xlVisible is the proper enum, but you get the idea. – FreeMan Apr 30 '15 at 15:39 IF Range.SpecialCells(xlVisible).Count xlVisible All I am trying to do is to see if x list is in y list. I just want any zeros. So not sure if I can split it that way – Sam ...