Select Celss in Excel

[billheath]

I am trying to write a macro to select a row of cells beginning with the currently selected cell. I want to covert the entire row from the formulas to the resulting values. So far,

ActiveCell.Select

ActiveWindow.SmallScroll Down:=-84
Selection.Copy

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub

This only converts the top cell. the rest are unchanged. Aparrently, the other cells are not being copied.

Thanks for your help. Bill

 

[SkipVought]

Hi,

Do you really ONLY want to convert ONE row?

It makes logical sense to do the entire table.

Otherwise you'd have a table of mixed row types.

 

[SkipVought]

With Selection.CurrentRegion
   .Copy
   .pasteSpecial xlPasteValues
End With

 

[billheath]

Thanks for you reply. I do have to convert one row at at time. Each row represents values for that particular day. What I am tying to do is select the particular day which is labeled in the top cell and then have the macro convert all the equations for that day. The formulas in each cell only capture the data for the current day. It is lost on the next day.

 

[SkipVought]

so what happens if you were to miss a day (or more)? Not a very good system IMNSHO.

I'd be more apt to include a Date in your source data. Then you could do the following: 1) modify your formula to include the date as a criteria, 2) eliminate converting formulas to values and 3) eliminate all the extra unused rows in your table (YUK!) This assumes making your table a Structured Table, that has lots of great features!

With Intersection(Selection.EntireRow, ActiveSheet.UsedRange)
   .Copy
   .pasteSpecial xlPasteValues
End With

 

[billheath]

Thanks. But when I try to run this, I get The error "Sub or Function not defined" referring to the word "Intersection

 

[billheath]

SkipVought
I modified your code to read:

Set intersection = Selection.EntireColumn
With intersection
.Copy
.PasteSpecial xlPasteValues
End With
End Sub

This works, but I only need to convert the top 100 values in the selected column, not the entire column. Any suggestions?
Thanks, Bill

 

[SkipVought]

Sorry Bill, the keyword is Intersect

 

[billheath]

It worked! but I only need to convert the top 100 values in the selected column, not the entire column. Any suggestions?
Thanks, Bill

 

[SkipVought]

t converts whatever crows you've made a selection in.

You select 100 cells in 100 rows, those get acted on.

 

[billyjoemc]

VBA process keeps bugging me. The full 2012 version is just lying in my laptop not been used

 

[SkipVought]

Do you mean by that?