Sunday, 15 January 2017

Delete rows based on criteria using an input box

Today I was asked the following question

I was on your course the other day, and was wondering if you could help.

say i have a table of data, and i want a button to delete certain rows if column a begins with a certain letter is that possible? 


so say i wanted to delete every row, if column a started with the letter B is that possible?

cheers Charlie

Only to glad to help Charlie as I said on the course we offer support after the course and we mean that.
So Here is the code to do that just copy and paste to a VBA module in excel the code searches column A for any Text beginning with the letter you specify on the input Box, if you forget and click ok a message will come up and abort the process leaving your data in tact

Sub Delete_with_Autofilter()
    Dim DeleteValue As String
    Dim rng As Range
    Dim calcmode As Long
   'sets the varible to accept data from inputbox
    Dim remove As Variant
    With Application
        calcmode = .Calculation
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
    End With
    'Fill in the value that you want to delete
                'Tip: with AutoFilter you can also use wildcards.
                '"*ron"  End with ron
                '"ron*"  Start with ron
                '"*ron*" ron is a part of the string
                'Tip: use DeleteValue = "<>ron" to delete rows without ron
                'You can also use the wildcard ? for a single character.
                'Note: in the examples I use the range  .Range("A1:A" & .Rows.Count)
                'Remember that A1 of this range is your header cell.
    'invokes inputbox adds wild card to the end
    remove = InputBox("Enter the value to start with") & "*"
    'the if function is added to make sure you enter a start value if only wild card all data deleted
If remove = "*" Then
    MsgBox "Warning!!! no start value selected aborting", vbCritical, "Avoid Delete of All Data"
    'change the delete value below in this scenario remove is a variable that accepts data from input box
    DeleteValue = remove
End If
    'Sheet with the data, you can also use Sheets("MySheet")
    With ActiveSheet
        'Firstly, remove the AutoFilter
        .AutoFilterMode = False
        'Apply the filter
        .Range("A1:A" & .Rows.Count).AutoFilter Field:=1, Criteria1:=DeleteValue
        With .AutoFilter.Range
            On Error Resume Next
            Set rng = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
            On Error GoTo 0
            If Not rng Is Nothing Then rng.EntireRow.Delete
        End With
        'Remove the AutoFilter
        .AutoFilterMode = False
    End With
    With Application
        .ScreenUpdating = True
        .Calculation = calcmode
    End With
End Sub

Saturday, 14 January 2017

InDesign Quick Start Up and Running

Adobe InDesign Quick Start Guide

InDesign is advanced desktop publishing software that allows you to create posters, flyers, brochures, and multipage documents such as booklets and magazines with text and pictures.

1) Opening InDesign and starting a new document 2) Navigating around InDesign 3) Inserting Text 4) Inserting Images 5) Inserting and Deleting Pages 6) Saving your document

 Opening InDesign and starting a new document

When you open InDesign, you will have the option of opening an existing document or creating a new document, book, or library.

When creating a new document, you will have to specify if it is a document for online use or print use. You will specify the number of pages in the document, and columns on each page, which you can go back and change once you’ve started the document.

 A document is what you will usually want to create; it is a basic InDesign file that you can make into a flyer, booklet, poster, brochure, etc.

A book is used if you want to combine multiple InDesign documents together into one long, multipage document.

A library is where a home user can store objects like logos that they use frequently and want available for all of their documents.

You will have to specify the page size. It defaults to standard letter-size paper, but it shows the dimensions not in inches, but in picas, a unit used in publishing. There are 6 picas to an inch and 12 points in a pica. 

If you’re entering a custom page or margin size, you can enter the size in inches by typing, for example, 8in or 8" for 8 inches and it will convert the inches to picas for you. 

 Navigating around InDesign

To jump between pages in your document, you can click the drop-down arrow next to the current page number at the very bottom left corner of the page:

You can also use the Pages panel, found on the right side of the screen near the top of the page. Double-click the thumbnail of the  page you want to work on.

To zoom in and out, you can press Command and “+” or Command  and “–“  on your keyboard, or use the zoom tool in the toolbar on the left edge of the  screen.

To pan around, use the panning tool in the toolbar on the left edge of the screen. Once your cursor becomes a little hand, you will be able to click and drag to move your page around.

  Inserting Text

 To type text in your document, you must first create a text frame.   First click the text tool in the toolbar on the left edge of the screen.   Then click and drag in your document to draw the text frame.

Once you have drawn the frame, just click inside to type. To make changes to the text, highlight it first. You can access the text tools by clicking on the button with the letter “A” in it, at the left edge of the top toolbar. This will give you a number of text options, such as changing the font type, size, and colour.

To make changes to the text at the paragraph level (things like indenting and centring), highlight the text and click the paragraph symbol at the left edge of the top toolbar.

Pan Zoom

Text tool

Font colour

Click to access font tools Bold and Italics in here

Click to access paragraph tools

Leading (similar to line spacing)

Centre Bulleted List Indents

Inserting Images

To insert an image, first go to the File menu at the top of the page and choose Place.

 You will be prompted to browse to your image file.

Once you click on your image file and click Open, a thumbnail of your image will be attached to your cursor. Just place your mouse wherever you want your image to appear and click once to insert the image into your document. You can move the image once it has been placed by clicking and dragging it.

To crop an image, click on any of the handles (little circles) surrounding the selected image. Click and drag towards the middle of the image to crop.

 To resize an image, hold down the Command key and click and drag any of the handles. Drag towards the centre of the image to make it smaller, and away from the centre to make it bigger. This can sometimes result in a distortion of the height-to-width ratio of the picture; to resize and keep the ratio the same, hold down the Command key and the Shift key and click and drag a handle.

To place a border around an image, select the image and click the Stroke button (looks like a box with a slash through it). You will be given several border colours to choose from.

 Inserting and Deleting Pages

You can insert and delete pages by going to the Layout Menu and choosing Pages. You will be prompted to enter the number of the page you wish to delete or the location where you want to the new page. You can choose Move Pages if you want to rearrange the page order.

 You can also insert and delete pages in the Pages panel on the right side of the screen. You can drag the thumbnail of the page you want to delete to the trash can in the bottom right corner of the panel. To add a page, first click once on the existing page that you want to have come before the new page. Then click the add page (second from the right) button at the bottom of the panel. The new page will appear directly after the thumbnail that was selected.

  Saving your document

To save your document as an InDesign document (with a file extension of .indd), go to the File menu and choose Save. However, if you save to this format, you will have to come back to the library or use another computer with InDesign CS5 or later if you want to edit it. 

You can also save your document as a PDF (Portable Document Format), which will make a perfect copy of your document that you can read or print from any computer, but you will not be able to edit it.  To save as a PDF, you have to Export the document by clicking the File menu and then choosing Export.

  When exporting an InDesign document, you can choose to export not only to PDF, but to a variety of file types, such as html to save it as a file suitable for use as a web page, or jpeg to save it as an image.

 When exporting to a PDF, you will choose between Print (choose if you plan to print out the document) and Interactive (choose if you plan to place the document online and would like to have interactive elements such as hyperlinks) format.

 When exporting, and saving your files, you can save them to a personal storage device (such as a flash drive). You can save a file and e-mail it to yourself, or save it to a free cloud storage website, which allows you to upload a file and access and download it on any Internet-connected computer. Popular cloud storage sites include Dropbox ( and OneDrive(office 365)). 

Shortcuts for Working with Type & Navigating Through and Editing Text in InDesign

Working with Type

All caps (on/off)
Small caps (on/off)
Shift+Ctrl+x/ Shift+Alt+Ctrl+x
Reset horizontal or vertical scale to 100%
Shift+Ctrl+L, R, /c
Align left, right, or center
Shift+Ctrl+F (all lines) / J (all but last line)
Justify all lines
Increase or decrease point size*
Shift+Ctrl+Alt+ >/<
Increase or decrease point size by five times*
Alt+Arrow Up/Arrow Down
Increase or decrease leading (horizontal text)*
Alt+Ctrl+ Arrow Up/+Arrow Down
Increase or decrease leading by five times (horizontal text)*
Auto leading
Align to grid (on/off)
Auto-hyphenate (on/off)
Alt+ Arrow Left/Arrow Right
Increase or decrease kerning and tracking (horizontal text)
Alt+Ctrl+ Arrow Left/Arrow Right
Increase or decrease kerning and tracking by five times (horizontal text)
Increase kerning between words*
Decrease kerning between words*
Clear all manual kerning and reset tracking to 0
Shift+Alt+ Arrow Up/+Arrow Down
Increase or decrease baseline shift** (horizontal text)
Shift+Alt+Ctrl+ Arrow Up/Arrow Down
Increase or decrease baseline shift by five times (horizontal text)
Shift-click loaded text icon
Automatically flow story
Alt-click loaded text icon
Semi-automatically flow story
Recompose all stories
Insert current page number

Navigating Through and Editing Text

Arrow Right/Arrow Left
Move to right or left one character
Arrow Up/Arrow Down
Move up or down one line
Ctrl+ Arrow Right/Arrow Left
Move to right or left one word
Move to start or end of line
Ctrl+Arrow Up/Arrow Down
Move to previous or next paragraph
Move to start or end of story
Double-click word
Select one word
Shift+ Arrow Right/Arrow Left
Select one character right or left
Shift+ Arrow Up/+Arrow Down
Select one line above or below
Select start or end of line
Triple-click / quadruple-click paragraph, depending on Text Preferences setting
Select one paragraph
Shift+Ctrl+ Arrow Up/Arrow Down
Select one paragraph before or after
Select current line
Select characters from insertion point
Shift+Ctrl+ Home/Ctrl+End
Select start or end of story
Select all in story
Shift+Alt+Ctrl+ Page Up/Page Down
Select first/last frame
Alt+Ctrl+ Page Up/Page Down
Select previous/next frame
Delete word in front of insertion point (Story Editor)
Update missing font list

InDesign Shortcuts Selecting and moving objects

Selecting and moving objects

Any tool (except selection tools)+CtrlTemporarily select Selection or Direct Selection tool (last used)
Direct Selection tool+Alt; / Pen, Add Anchor Point, / Delete Anchor Point tool+Alt+CtrlTemporarily select Group Selection tool
Esc/ double-clickSelect container of selected content
Shift+Esc/ double-clickSelect content of selected container
Selection, Direct Selection, / Group Selection tool+Shift-click (to deselect, click center point)Add to or subtract from a selection of multiple objects
Selection, Direct Selection, / Group Selection tool+Alt-drag*Duplicate selection
Alt+Arrow KeysDuplicate and offset selection
Alt+Shift+Arrow KeysDuplicate and offset selection by 10 times**
Arrow KeysMove selection**
Ctrl+Shift+Arrow KeysMove selection by 10th**
Shift+Arrow KeysMove selection by 10 times**
Selection / Direct Selection tool+Ctrl+Shift-clickSelect master page item from document page
Selection tool+Ctrl-click, / Selection tool+Alt+Ctrl-clickSelect next object behind or in front
Alt+Ctrl+ Page Down/Page UpSelect next or previous frame in story
Shift+Alt+Ctrl+ Page Down/Page UpSelect first or last frame in story
Ctrl+[Send object backward
Shift+Ctrl+[Send object to back
Ctrl+]Bring object forward
Shift+Ctrl+]Bring object to front
*Press Shift to constrain movement to 45° angles.
**Amount is set in Edit > Preferences > Units & Increments

InDesign Shortcut Keys Tools


V, EscapeSelection Tool
aDirect Selection tool
Ctrl+TabToggle Selection and Direct Selection tool
Shift+pPage tool
uGap tool
pPen tool
=Add Anchor Point tool
-Delete Anchor Point tool
Shift+cConvert Direction Point tool
tType tool
Shift+tType On A Path tool
nPencil tool (Note tool)
\Line tool
fRectangle Frame tool
mRectangle tool
lEllipse tool
rRotate tool
sScale tool
oShear tool
eFree Transform tool
iEyedropper tool
kMeasure tool
gGradient tool
cScissors tool
hHand tool
Spacebar (Layout mode), Alt (Text mode), /Alt+Spacebar (both)Temporarily select Hand tool
zZoom tool
Ctrl+SpaceTemporarily select Zoom In tool
xToggle Fill and Stroke
Shift+xSwap Fill and Stroke
jToggle between Formatting Affects Container and Formatting Affects Text
, (comma)Apply Color
. (period)Apply Gradient
/Apply No Color
wSwitch between Normal View and Preview Mode
Shift+gGradient Feather tool

Create a scamp Document for InDesign

What is scamp?

Preliminary design or layout of an advertisement or other promotional material.

this is the very beginning of your design concept

you will receive a brief from your client you then create a mock up of that brief you then turn that into a story board or a design layout

When coming to layout your InDesign Project you can refer to the scamp to guide you through the set up and layout, thus reducing editorial time

Images from Gareth Davies

Set up InDesign to make your workflow easier

We can all use InDesign but lets create a Workspace that makes working with InDesign easier.
Select Essentials - Then select the menu again and select Reset Essentials

Now Drag the panels out so you have six individual panes

Lets star with the pages panel click the expand panel

The screen looks as shown below

Now drag layers towards the pages so that the status bar turns blue at this point release your mouse button and the layers will dock next to pages

we can now drag them to line up as shown below to start the character set drag the character styles to below the pages panel

Now you know how to dock the panels lay them out as shown below 

you can click on the collapse panel top right corner of the panel

The panel will now collapse

Click on the swatches tab to expand that panel

As you start using InDesign you will find this layout useful