Select all rows with data 1

[CrystalStart]

Every time my Macro is running it populated WKbook with different amount of data. Column headers are staying the same - rows this is what I need to count.
How do I select all data? Is this even possible?
Thanks so much

 

[SkipVought]

Hi,

If your data is contiguous, with headings in Row 1...

lRowCount = MySheet.[A1].CurrentRegion.Rows.Count

Skip,
[sub]
[red]Be advised:[/red] When you ignite a firecracker in a bowl of vanilla, chocolate & strawberry ice cream, you get...
Neopolitan Blownapart! [/sub]

 

[CrystalStart]

Will lRowCount show me the range or a number of rows?
Thanks

 

[CrystalStart]

Got it, thanks

 

[CrystalStart]

Now I have
Range("A1:AH15").Select
where 15 is a result of a function
RowsNum = Selection.CurrentRegion.Rows.Count
where RowsNum = 15

How do I make Range("A1:AH15").Select
where 15 will be a variable that will take RowsNum's value?

Thanks much

 

[CrystalStart]

I am trying to use Replace function but it does not work (syntax wise), please look into it


RangeX = varNbRows (Variable)
StrToReplace = Range("A1:AH15")

Replace(StrToReplace, Instr(StrToReplace 12,2), varNbRows)

 

[SkipVought]

Range(Cells(1, "A"), Cells(lRowCount, "AH").Select
[code]


Skip,
[sub]
[glasses] [b][red]Be advised:[/red][/b] When you ignite a firecracker in a bowl of vanilla, chocolate & strawberry ice cream, you get...
[b]Neopolitan Blownapart![/b] [tongue][/sub]

 

[CrystalStart]

I have dates in a first raw as
12/8/2004 12/13/2004 12/14/2004 12/15/2004 12/16/2004

How can I flip those too? So it is desc?
Thanks

 

[SkipVought]

Use paste Special - transpose

SomeRange.Copy
SomeOtherRange.PasteSpecial Transpose:=True

Skip,
[sub]
[red]Be advised:[/red] When you ignite a firecracker in a bowl of vanilla, chocolate & strawberry ice cream, you get...
Neopolitan Blownapart! [/sub]

 

[CrystalStart]

Yeah? Some range would be B column to the last column with data.
I've changed code to
lColumnCount = Sheets("Sheet1").[B1].CurrentRegion.Columns.Count
which gives me 15 but I need 'O' so I can Select Range(B:O)
So I am looking for the Name of the column Property

 

[CrystalStart]

I have to share it with you all.
This code works a treat!

Thanks for helping


Sub FlipColumns()
Dim vTop As Variant
Dim vEnd As Variant
Dim iStart As Integer
Dim iEnd As Integer

lColumnCount = Sheets("Sheet1").[B:B].CurrentRegion.Columns.Count

If lColumnCount = "" Then
MsgBox "There is no data."
Exit Sub
End If
Application.ScreenUpdating = False
iStart = 2
iEnd = lColumnCount
Do While iStart < iEnd
vTop = Selection.Columns(iStart)
vEnd = Selection.Columns(iEnd)
Selection.Columns(iEnd) = vTop
Selection.Columns(iStart) = vEnd
iStart = iStart + 1
iEnd = iEnd - 1
Loop
Application.ScreenUpdating = True
End Sub