How to find the values in a column? 2

[thepunisher]

Hi,

How do i select an entire column and find out how many values are in it?

Or even get the sum of all values in a column?

I will record this code, and then tailor it for myself!

thanks in advance,

thePunisher.

Chief Dan George-Get ready little lady, hell is coming to breakfast

 

[Molby]

In another column use sumif and countif,

=COUNTIF(A:A,"<>""")
=SUMIF(A:A,"<>""")

 

[SkipVought]

Hi,

Since this is the VBA forum...

Set oCol = ActiveSheet.Cells(1, 1).EntireColumn
TheCount = Application.COUNTIF(oCol, "<>""")
TheSum = Application.SUM(oCol)
Set oCol = Nothing

You do not need to SELECT, ACTIVATE in VBA Code. It slows down the processing. Usually the ONLY time that SELECT or ACTIVATE is necessary, it at the end of a process to display the Sheet & Cell that you want the user to see when everything is done.

Skip,

_{[red]Be advised:[/red]
Alcohol and Calculus do not mix!
If you drink, don't derive!}

 

[Molby]

For some reason I thought I was in the office forum, I need a holiday.....

 

[SkipVought]

Hey, Molby.

Take a break.

Have a cup o' java.

Put yer feet up. ;-)

BTDT!

Skip,

_{[red]Be advised:[/red]
Alcohol and Calculus do not mix!
If you drink, don't derive!}

 

[Molby]

Skip,

A break sounds like a fantastic idea, somewhere hot, by a pool with a beer...

 

[SkipVought]

Take the rest of the week off, on me! HA!

Skip,

_{[red]Be advised:[/red]
Alcohol and Calculus do not mix!
If you drink, don't derive!}

 

[thepunisher]

thanks guys,

i didnt need the code, found a different way to do it, it was a little bit more complicated than i had thought.

Just as a short question with regards the .Select method thingy, How would i simplify the code below:

'Copy 'LMP' values and paste to 'Summary' sheet.
Sheets("LMP").Select
Columns("G:G").Copy
Sheets("Summary").Select
Columns("H:H").Select
ActiveSheet.Paste
Application.CutCopyMode = False


Chief Dan George-Get ready little lady, hell is coming to breakfast

 

[RamziSaab]

Sub testing()
Worksheets("Sheet1").Columns("G:G").Copy
Worksheets("Sheet1").Columns("H:H").PasteSpecial
Application.CutCopyMode = False
End Sub

 

[Molby]

You can use

Worksheets("LMP").Range("G:G").Copy Destination:=Worksheets("Summary").Range("H:H")

 

[SkipVought]

  'Copy 'LMP' values and paste to 'Summary' sheet.
   Sheets("LMP").Columns("G:G").Copy Destination:=Sheets("Summary").[H1].Paste

Skip,

_{[red]Be advised:[/red]
Alcohol and Calculus do not mix!
If you drink, don't derive!}

 

[thepunisher]

thanks guys,

really helpful stuff.

Chief Dan George-Get ready little lady, hell is coming to breakfast