×
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!
  • Students Click Here

*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.

Students Click Here

Jobs

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

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

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

(OP)
Hi

Here is a bit of dummy data:


            A      B        C         D            E               F
1        SURNAME        NAME          EE NO       START DATE       TERMINATION DATE
2        BROWN          JOHN          12345       31/01/17         12/10/17
3        JONES          FRED          39492       14/07/17         15/12/17
4        SMITH          ANDREW        43243       01/02/16         12/06/17
5        ARDEN          MICKY         43980       12/05/16         10/07/17
6        GREEN          DAVID         47919       03/03/16        

 
At this point in my macro the data has been sorted by termination date so all those with dates in column F are at the top.

If I use
    Range("F2").Select
    Range(Selection, Selection.End(xlDown)).Select

 
It selects everything in column F with something in it - in this example F2 to F5.

What I'm trying to achieve is the equivalent of SHIFT SPACEBAR so that rows 2 to 5 are selected.

When using the macro recorder it gives me
Rows("2:5").Select
 

Which is fine for the current data, however, there will be future reports where there might be termination dates in say rows 2 to 10. It's this I need to account for which is why I'm trying to find something equivalent to SHIFT SPACEBAR. I've tried sendkeys but there's nothing for spacebar.

Does this make sense?



thank you for helping

____________
Pendle

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

2
First, you do not need to SORT your data.

Second, you do need to use the AutoFilter. I also generally convert ALL my tables to Structured Tables via Tables > Table.

Third, make visible only those rows where termination data is not blank.

Fourth, use my original code...

CODE

'
    With ActiveSheet
    'filter termination dates
        .ListObjects(1).Range.AutoFilter Field:=5, Criteria1:="<>"
    'delete visible data rows in table
        Range( _
          .Cells(2, 1), _
          .Cells(.Cells(2, 1).CurrentRegion.Rows.Count, 1)) _
        .EntireRow.SpecialCells(xlCellTypeVisible).Delete
    'show remaining data
        .Cells(1, 1).Select
        .ShowAllData
    End With 

But do you REALLY want to delete data? Data is usually pretty valuable. If you have a Filter, all you need do is filter out the non=blank data and then you have the ones not yet terminated.

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 done the trick.

Thank you very much for your help with this.

thank you for helping

____________
Pendle

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

Here's code using Structured Table references to delete the data...

CODE

Dim tName As String, tCol As Integer
    
    With ActiveSheet
        tName = .ListObjects(1).Name
        tCol = Range(tName & "[TERMINATION DATE]").Column
    'filter termination dates
        .ListObjects(1).Range.AutoFilter Field:=tCol, Criteria1:="<>"
    'delete visible data rows in table
        Range(tName & "[#Data]") _
        .EntireRow.SpecialCells(xlCellTypeVisible).Delete
    'show remaining data
        .Range(tName & "[#ALL]").Select
        .ShowAllData
    End With 

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!

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