Doc Sir the data can be more than in column U, there is a chance the data will be till column Z or more than that
Doc Sir the data can be more than in column U, there is a chance the data will be till column Z or more than that
I am trying to make the last column Dynamic & if I unable share the details then plz look at it tomorrow & help me for the same
Thnx Alot Doc Sir for helping me in solving the same
Have a Awesome Day
I am trying to make the last column Dynamic & if I unable share the details then plz look at it tomorrow & help me for the same
Thnx Alot Doc Sir for helping me in solving the same
Have a Awesome Day
Hi
Making the Last column , Lc, dynamic in your last macro , Sub OnlyHaveRowsWhereColumnCisNotEmpty()
In this case, the usual way we did it before, something like
Ws.Cells.Item(1, Ws.Columns.Count).End(XlToLeft).Column
or like
Ws.Cells.Item(3, Ws.Columns.Count).End(XlToLeft).Column
will not work sometimes
it will not work if , in those examples , row 1 or row 3 was empty, which, based on your test data, might have been the case.
( I am assuming in all this work that rows other than row 1 might have an empty cell in column C, and , like your test data , an entire row might be empty…… )
We can try something else
We can use the UsedRange property's available properties of
the start column, UsedRange.Column
and the columns count UsedRange.Columns.Count
Using
UsedRange.Columns.Count- UsedRange.Column + 1
That is half the solution to the dynamic column problem. It returns us the last column number, which for yout example files , should be 21 ( Column Letter U )
The next part of this problem is that my macro, Sub OnlyHaveRowsWhereColumnCisNotEmpty() , needs to use the column Letter , not the column number.
You need to include this function , ( which you have had this function already from me many times before )
That function mustCode:' https://excelfox.com/forum/showthread.php/1546-TESTING-Column-Letter-test-Sort-Last-Row?p=7214#post7214 Public Function CL(ByVal lclm As Long) As String ' http://www.excelforum.com/development-testing-forum/1101544-thread-post-appendix-no-reply-needed-please-do-not-delete-thanks-4.html#post4213980 Do: Let CL = Chr(65 + (((lclm - 1) Mod 26))) & CL: Let lclm = (lclm - (1)) \ 26: Loop While lclm > 0 End Function
Either
Go in the same code module as Sub OnlyHaveRowsWhereColumnCisNotEmpty[color=lightgreyDynamicColumns[/color]()
Or
It can go in any other module: Just to illustrate that point I have uploaded two sample workbooks,
macro1.xlsm has the function and the next macro version in it in the same code module.
Macro2.xlsm has them in separate modules, like this:
MainMacroAndFunctionCodeModules.JPG : https://imgur.com/siBZVuz
MainMacroAndFunctionCodeModules.JPG
The two changes for the dynamic column is
_1 a new line
Dim Lc As Long: Let Lc = Ws.UsedRange.Columns.Count - Ws.UsedRange.Column + 1
_2 Modify the column indicia code line, Clms() = Evaluate("=Column(A:U)")
Clms() = Evaluate("=Column(A:" & CL(Lc) & ")")
_3 You need to include the function CL( )
The next macro version and function, Sub OnlyHaveRowsWhereColumnCisNotEmptyDynamicColumns() , is here:
https://excelfox.com/forum/showthrea...ll=1#post14663
https://excelfox.com/forum/showthrea...ge29#post14663
and also in the files:
macro1.xlsm : https://app.box.com/s/tl3rs9693jwuv9c2w36ok8fpaewuf0ta
macro2.xlsm : https://app.box.com/s/t35238lm19bj6y0p6m6p68uaknsdf37z
Alan
Last edited by DocAElstein; 07-19-2020 at 02:33 PM.
A Folk, A Forum, A Fuhrer ….
Thnx Alot Doc Sir for helping me in solving this problem
Problem Solved
Have a Awesome Day Bro
Only a small thing is missing, i want to save the changes also but i am unable to make it
i mentioned wb.save but it is not successful, So i need help regarding the same
What exactly did you try? You must take your time and explain everything more carefully, or you waste my time with lots of unecerssary replies...
I want to save the output sir
the macro is runing perfect but in the last we have to mention lines that will save the changes
You did not answer my question
Last edited by DocAElstein; 07-19-2020 at 07:45 PM.
A Folk, A Forum, A Fuhrer ….
Code:Sub STEP8() Dim arrWbs() As Variant Let arrWbs() = Array("C:\Users\WolfieeeStyle\Desktop\A.xlsx", "C:\Users\WolfieeeStyle\Desktop\Files\B.xlsx") Dim Wb As Workbook, Ws As Worksheet Dim Stear As Variant For Each Stear In arrWbs() ' 2a Worksheets data info Set Wb = Workbooks.Open(Stear) Set Ws = Wb.Worksheets.Item(1) Dim LrC As Long: Let LrC = Ws.Range("C" & Ws.Rows.Count & "").End(xlUp).Row Dim Lc As Long: Let Lc = Ws.UsedRange.Columns.Count - Ws.UsedRange.Column + 1 Dim arrC() As Variant: Let arrC() = Ws.Range("C1:C" & LrC & "").Value2 Dim Cnt As Long For Cnt = 1 To LrC Dim strRws As String If arrC(Cnt, 1) <> "" Then Let strRws = strRws & Cnt & " " Next Cnt Let strRws = Left(strRws, Len(strRws) - 1) Dim Rws() As String: Let Rws() = Split(strRws, " ", -1, vbBinaryCompare) Dim RwsT() As Variant: ReDim RwsT(1 To UBound(Rws) + 1, 1 To 1) For Cnt = 1 To UBound(Rws) + 1 Let RwsT(Cnt, 1) = Rws(Cnt - 1) Next Cnt Dim Clms() As Variant ' Let Clms() = Evaluate("=Column(A:" & CL(Lc) & ")") Dim arrOut() As Variant Let arrOut() = Application.Index(Ws.Cells, RwsT(), Clms()) Ws.Cells.ClearContents Let Ws.Range("A1").Resize(UBound(arrOut(), 1), 21).Value2 = arrOut() Let strRws = "" Next Stear arrWbs.Save 'Here we have to mention lineswhich will save the changes done by the macro, I tried by wb.save also & arrWbs.Save also but it was not correct arrWbs.Close 'Here we have to mention lineswhich will close all the files opened by macro End Sub Public Function CL(ByVal lclm As Long) As String Do: Let CL = Chr(65 + (((lclm - 1) Mod 26))) & CL: Let lclm = (lclm - (1)) \ 26: Loop While lclm > 0 End Function
Plz see the code & my remarks
I have mentioned the problem Doc Sir in the remarks
OK, you seem to be trying to save all the files at once. That is not so easy to do.
arrWbs() is just text. Inside it, it just looks like this:
" C:\Users\WolfieeeStyle\Desktop\A.xlsx _ , _ C:\Users\WolfieeeStyle\Desktop\Files\B.xlsx "
You can’t apply any property action command to a piece of text
In my macro, each time the loop is done, the Workbook object, Wb is made from the string path and name of a workbook, ( which is held each time in the variable Stear ).
You know how to save and close a single Workbook with like
Wb.Save
Wb.Close
So you need to have those code lines inside the loop which is done For every workbook
So then Each time it will save Each workbook
Code:Wb.Save Wb.Close Next Stear End Sub
Example in this file:
Macro2.xlsm : https://app.box.com/s/t35238lm19bj6y0p6m6p68uaknsdf37z
That’s all I have time for today.
Before you post any questions again, think more carefully , and read what I write…
Last edited by DocAElstein; 07-19-2020 at 07:49 PM.
A Folk, A Forum, A Fuhrer ….
Problem Solved
Thnx Alot Doc Sir For helping me in solving this Problem Sir
Have a Great Day
Last edited by DocAElstein; 07-26-2020 at 03:18 PM.
Bookmarks