INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Excel 2013: Application.onkey - using shift and spacebar to select some rows

Excel 2013: Application.onkey - using shift and spacebar to select some rows

Excel 2013: Application.onkey - using shift and spacebar to select some rows

(OP)
Hello

In my macro, I sort by 'leaving date' all those leavers which I then want to remove.

Once sorted, I select all the dates using

Range(Selection, Selection.End(xlDown)).Select 

Now manually recording the macro, I use Shift and Spacebar to then select all the rows and then delete them.

But the recorded code specifies the rows - eg 2-235, but in another version of the report, the leavers might go up to row 300.

I've had a look in the help and there is the code
application.onkey 
but it doesn't seem to include a spacebar option.

Am I trying to do something impossible?

thank you for helping

____________
Pendle

RE: Excel 2013: Application.onkey - using shift and spacebar to select some rows

Hi,

CODE

'
    With ActiveSheet
        Range( _
          .Cells(2, 1), _ 
          .Cells(.Cells(2, 1).CurrentRegion.Rows.Count, 1)) _
        .EntireRow.SpecialCells (xlCellTypeVisible).Delete
    End With 


Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Excel 2013: Application.onkey - using shift and spacebar to select some rows

(OP)
That's great, thank you very much

thank you for helping

____________
Pendle

RE: Excel 2013: Application.onkey - using shift and spacebar to select some rows

(OP)
Hello

I'm needing to return back to this as the code:

  With ActiveSheet
        Range( _
          .Cells(2, 1), _ 
          .Cells(.Cells(2, 1).CurrentRegion.Rows.Count, 1)) _
        .EntireRow.SpecialCells (xlCellTypeVisible).Delete
    End With 

is actually deleting all the rows in the worksheet rather than the selected ones.

What is running before is:

Find the column with the termination dates and sort into A-Z (the dates are all together)

  Range("L2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Rows("2:331").Select
    Range("L2").Activate
    Selection.Delete Shift:=xlUp
    Range("A2").Select
 

However, while in this case rows 2:331 have termination dates in this and these are the rows I don't want, it may be that in a further version of the report rows 2:250 are terminations.

The code SkipVought kindly provided is actually deleting all the worksheet. I tried to use the

       Rows.EntireRow.Select 

but that also is selecting the entire worksheet rather than the terminated dates.

Can someone point me in the right direction?

thank you for helping

____________
Pendle

RE: Excel 2013: Application.onkey - using shift and spacebar to select some rows

For some reason, I believe I was thinking that you were using a FILTER to select the date range in your table, ie to make Termination Dates visible.

Quote:

Once sorted, I select all the dates using

Range(Selection, Selection.End(xlDown)).Select

Now manually recording the macro, I use Shift and Spacebar to then select all the rows and then delete them.

How is your macro to know what row to stop at?

What is the logical definition of Termination Date?

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Excel 2013: Application.onkey - using shift and spacebar to select some rows

(OP)
Hi

In my manually recorded macro I highlight the column names row and switch on filters.

Then search for termination and sort A-Z smallest to largest and that puts the ones with dates at the top.

When recording the deletion the code looks like this:

Rows("2:331").Select

and the CTRL Spacebar highlights the rows and removes it.

So if I alter the procedure to ignore blanks and then use your original code to delete the lines?

thank you for helping

____________
Pendle

RE: Excel 2013: Application.onkey - using shift and spacebar to select some rows

Without seeing your data, I cannot advise.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close