Posts by MFiggat

    This works amazing, I am assuming that the .Columns(#) addition will always reference the amount of columns from the first column of the worksheet. I am going to look into this a bit more to understand how it works, I may pop back to ask any questions i am not understanding. But for the purposes of answering my initial question, you did. Thank you very much.

    Carim,


    This selected all cells in the report to the last used column. Which will work in some of the situations i need to use this. So thank you a ton.


    But how could i adjust this to select only the column that has the active filter?

    Hello all and Thank you in advanced


    I am trying to select all visible cells in a column that has been filtered. The "Delta_length" variable is 56 and when the column is filtered how i want it there are exactly 56 rows. But when i pass through "Range(ActiveCell.Offset(0, 0), ActiveCell.Offset(Delta_length, 0)).SpecialCells(xlCellTypeVisible).Select"
    I get a selection from the first visible cell down 56 rows of the WHOLE worksheet, not the filtered rows like I require. Please help me identify how i can make the range work only on the visible filtered cells. ***The "Delta_length" variable may not be 56 next time i run this report.


    [VBA]Dim Delta_length As Long
    Delta_length = Range("AQ1").Value
    'Next Portion locates first Visible cell in the filtered column
    Range("B2").Select
    ActiveCell.Offset(1, 0).Select
    Do Until ActiveCell.EntireRow.Hidden = False
    ActiveCell.Offset(1, 0).Select
    Loop
    'Next portion should select from first visible cell in filtered column 56 visible rows down. But does not
    Range(ActiveCell.Offset(0, 0), ActiveCell.Offset(Delta_length, 0)).SpecialCells(xlCellTypeVisible).Select[/VBA]

    Appreciate the responses. I played around with the method i initially used and was able to get the correct range showing using [VBA]Range(ActiveCell.Offset(0, 0), ActiveCell.Offset(Unconfirmed_range, 0))[/VBA] and also that i was not setting up my variables correctly because i was doing [VBA]Unconfirmed_range = ("AY1")[/VBA], instead of [VBA]Unconfirmed_range = Range("AY1").Value[/VBA].


    Again i appreciate your responses and hope to be writing VBA like yours in the future as i continue to learn.

    Thank you for the response. But no, though i am showing the full length of the current sheet (300 cells currently) i will adjust that too to be dynamic to how many rows there are each time i run the report.


    So i am looking for a way for the variable i create "Unconfirmed_range" to determine how many cells are in the selected range. [VBA]Range("G2:G" & Unconfirmed_range).Select[/VBA], as i am messing around with it right now this makes it select all cells in the rows G1 and G2. So still looking for the correct way. Thanks.

    Hello, My efforts to have a Macro select a range of cells in a filtered column have provided two obstacles;


    [VBA]'CREATING VARIABLES
    Dim Unconfirmed_range As Variant
    Unconfirmed_range = ("AY1")
    Dim Partially_range As Variant
    Partially_range = ("AZ1")
    Dim SMG_PO_range As Variant
    SMG_PO_range = ("BB1")
    'BEGINING THE FORMATTING
    ActiveSheet.Range("$A$1:$BD$300").AutoFilter Field:=7, Criteria1:= _
    "Unconfirmed - Blank AB Date"
    Range("G1").Select
    ActiveCell.Offset(1, 0).Select
    Do Until ActiveCell.EntireRow.Hidden = False
    ActiveCell.Offset(1, 0).Select
    Loop
    ActiveCell.FormulaR1C1 = "Unconfirmed"
    Selection.Copy
    Range("G:G" & Unconfirmed_range).Select
    ActiveSheet.Paste[/VBA]


    For reference, the "Unconfirmed_range" variable is the amount of cells that show "Unconfirmed" in the cell text, which is 187 cells this time. The Countif formula is in Cell "AY". But next time i run this report it could be any number so i am trying to make this flexible.


    Since the column is filtered i have used the Loop to reference the first visible cell after the Header Row in G1, adjust it and then copy it. But when I attempt to select the range i want to paste, I run into trouble on line [VBA]Range("G:G" & Unconfirmed_range).Select[/VBA]


    How do i have the range selection be from the first visible cell below the header (the one i have copied the value I intend to paste) to the end of the variable (which 187 cells down this time).


    As i mess around with it, i get the whole G:G column selected, or everything after G1 to the end of the worksheet selected or just an error message. Thank you in advance for your help.