logo elektroda
logo elektroda
X
logo elektroda
Advanced Search
logo elektroda

Selecting Last Column Range in VBA: Using .Cells, Columns.Count & .End(xlToLeft) Methods

Forte12 5034 6
ADVERTISEMENT
Treść została przetłumaczona polish » english Zobacz oryginalną wersję tematu
Reply | New topic
  • #1 16426741
    Forte12
    Level 8  
    how to select the range to the last column in one row

    ost_column = .Cells (2, Columns.Count) .End (xlToLeft) .Column
  • ADVERTISEMENT
  • #2 16426862
    JRV
    VBA, Excel specialist  
    Code: text
    Log in, to see the code


    Added after 6 [minutes]:

    d2 will be a value, necessarily a formula?
  • ADVERTISEMENT
  • #3 16426974
    Forte12
    Level 8  
    does not work, unfortunately

    Added after 6 [minutes]:

    Cells (2, 4) = Application.WorksheetFunction.Average (Range (Cells (2, 5), Cells (2, ost_column))) I have entered without dots and it works. Only this is a value and I had it done so that it copied functions to the last line and now it doesn't work anymore
  • ADVERTISEMENT
  • #4 16426999
    JRV
    VBA, Excel specialist  
    Forte12 wrote:
    ost_column = .Cells (2, Columns.Count) .End (xlToLeft). Column
    it was full stop because you used With Sheets ("Medium")

    You must use R1C1 notation for the formula
  • ADVERTISEMENT
  • #5 16427002
    Forte12
    Level 8  
    I combine with for loop
    Code: VBScript
    Log in, to see the code


    I did something like that
  • Helpful post
    #6 16427021
    JRV
    VBA, Excel specialist  
    Forte12 wrote:
    For k = 5 This is last_data
    it makes sense? to change columns from 5 is how many rows

    Added after 1 [minutes]:

    .Cells (w, 4) .Value = Application.WorksheetFunction.Average (Range (.Cells (w, 5), .Cells (w, ost_column)))

    Added after 1 [minutes]:

    For k = 5 To ost_dane and Next delete
  • #7 16427037
    Forte12
    Level 8  
    so I did :)
    Code: VBScript
    Log in, to see the code


    Added after 1 [minutes]:

    Thank you for your help :) I only have the export of the file left but I don't know if it will work :( I need to export a file in ASCII format.
Reply | New topic
Report a violation of the law

Topic summary

The discussion revolves around selecting the range to the last column in a specific row using VBA in Excel. The main question involves using the .Cells, Columns.Count, and .End(xlToLeft) methods to achieve this. Various users provide insights and solutions, including the use of Application.WorksheetFunction.Average to calculate averages across specified ranges. A key point noted is the importance of using the correct syntax, particularly when using the With statement for referencing sheets. The author successfully implements a loop to average values from the fifth column to the last column, expressing gratitude for the assistance received. Additionally, there is a mention of exporting the file in ASCII format.
Summary generated by the language model.
ADVERTISEMENT