Excel - Ctrl +Arrow

Hi

In Excel 2007, I used a formula to fill select cells in columns in A and B. I then copy, paste special - values.

Unfortunately, the CTRL+ down arrow does not work in taking me to the next filled cell. I automatically go to the bottom most cell where the formula was implemented (e.g. row 66000).

Please advise.

Thank you
tahirihAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

zorvek (Kevin Jones)ConsultantCommented:
CTRL+DOWN ARROW works in my version of 2007.

If "SCRL" appears in the status bar press the Scroll Lock key (sometimes called "ScrLk") to turn off the scroll lock option. The Scroll Lock key is sometimes the same key as the Pause/Break key and is usually located in the upper right corner of the keyboard.

Kevin
zorvek (Kevin Jones)ConsultantCommented:
Actually, CTRL+Arrow may not be what you think it is. It is supposed to move the selection to the edge of the current data region where a data region is a range of cells that contains data and that is bounded by empty cells or worksheet borders. It has always been this way.

Can you describe more precisely what you want to do?

Kevin

tahirihAuthor Commented:
No, this still did not work, please advise.
OWASP: Threats Fundamentals

Learn the top ten threats that are present in modern web-application development and how to protect your business from them.

zorvek (Kevin Jones)ConsultantCommented:
If it does not work as I described then you did not successfully convert the formulas to values. Or the "empty" cells contain a space.

Kevin
tahirihAuthor Commented:
Ok, then this makes sense, because the formula I used populates a " " if the condition is False. How can I modify this?
zorvek (Kevin Jones)ConsultantCommented:
Change the formula to produce an empty string "" instead of a space " ".

Kevin
tahirihAuthor Commented:
still not working.
zorvek (Kevin Jones)ConsultantCommented:
You have to change the formula AND convert the formulas to values.

Kevin
tahirihAuthor Commented:
i did this last step - using copy - paste special - values
zorvek (Kevin Jones)ConsultantCommented:
Check the values that should be empty. Do they contain a space?

Kevin
tahirihAuthor Commented:
Please see the attached spreadsheet, If Column b is 'DRG', then A has the formula to be 'DRG' - and it is A that i am having issues with.
full-mdcs-00-07.xlsx
zorvek (Kevin Jones)ConsultantCommented:
I see the problem now. I had forgotten about this. A cell is not truly empty when it is an empty string resulting from a formula, even after converted to values only.

The only way to fix this problem is with VBA. Add the following macro to your workbook and run the macro.

Public Sub ResetEmptyCells()

' Reset all blank cells including cells with single apostrophies to empty
' cells.

   Dim Cell As Range
   
   If ActiveSheet.Type <> xlWorksheet Then Exit Sub
   
   For Each Cell In ActiveSheet.UsedRange
      If Len(Cell) = 0 And Len(Cell.Formula) = 0 Then Cell = vbNullString
   Next Cell
   
End Sub

Kevin
tahirihAuthor Commented:
is there a way this code can be modified to run on select columns - it appears to be stuck
tahirihAuthor Commented:
For example, I would like to run the macro on columns A - C only. This should take less time. Thank you
zorvek (Kevin Jones)ConsultantCommented:
Yes.

Public Sub ResetEmptyCells()

' Reset all blank cells including cells with single apostrophies to empty
' cells.

   Dim Cell As Range

   Application.ScreenUpdating = False
   Application.CalculationMode = xlCalculationManual
   
   For Each Cell In Selection
      If Len(Cell) = 0 And Len(Cell.Formula) = 0 Then Cell = vbNullString
   Next Cell
   
   Application.ScreenUpdating = True
   Application.CalculationMode = xlCalculationAutomatic

End Sub

Kevin

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
zorvek (Kevin Jones)ConsultantCommented:
The above will operate only on the selected cells.

Kevin
tahirihAuthor Commented:
Sorry for my lack of understanding - but where do I enter columns A - C in the most recent code you posted. Thank you.
zorvek (Kevin Jones)ConsultantCommented:
Select the columns, or better, select just the cells you want to edit in those columns and then run the macro.

Kevin
tahirihAuthor Commented:
The code is running on the columns - still taking time. How can I tell how much more remains?
tahirihAuthor Commented:
how can i select columns B and C from row 1 to row 66000 in the upper left box. thank you
zorvek (Kevin Jones)ConsultantCommented:
Select cell B1. Press SHIFT+RIGHT ARROW. Press SHIFT+CTRL+DOWN ARROW.

Kevin
tahirihAuthor Commented:
This  takes me all the way down to the bottom of the sheet (row 100000).
zorvek (Kevin Jones)ConsultantCommented:
That is not the bottom. That is the last row where you copied your formula and then converted the formulas to values. You need to convert all of those rows.

The actual last row of the worksheet is row 1,048,576.

Kevin
tahirihAuthor Commented:
it still takes so long...iis this normal?
zorvek (Kevin Jones)ConsultantCommented:
There are a lot of cells to edit. Next time don't copy the formula down as far.

Kevin
tahirihAuthor Commented:
Thank you
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.