Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

basic offset and skip

Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » Basic offset and 'skip blanks'

Basic offset and 'skip blanks'

ResolvedVersion 2010
Edited on Thu 24 Jan 2013, 14:01

Alistair has attended:
Excel VBA Intro Intermediate course
Excel VBA Intermediate course

Basic offset and 'skip blanks'

Hello, I am still very much learning VBA after the course, so the methods I am using may not be the most logical but hopefully your be able to help me solve a couple of issues I am having.

I have a block of data, which is always 18 columns in width but a varying amount of rows. This data is imported from a CSV file and I am trying to re-organise the data. The header of the 18 columns is initilas (3 letters). The rows beneath are either a "1" or a "0". I am using the formula =

If(Q4=1,Q$3,"")


Using VBA I am trying to copy this formula to the 18 colums and to however many rows there are; this could be 40-80 but varying each time.

My code, below so far, is probably a little confused as I have been trying to work this out for a couple of hours...

Sub NextCopyDataOver()
'From now on it is reorganise of initials

Dim CountR As Integer

CountR = 0

Sheets("organise").Select
Range("AK4").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-20]=1,R3C[-20],"""")"
Selection.Copy

ActiveCell.Offset(1, 0).Select


Do Until ActiveCell.Offset(1, -3) = ""
ActiveCell.Offset(1, 0).Select
ActiveSheet.Paste

Loop


End Sub


The code simply copies the formula down the one column and not across all 18 columns.

Thoughts?

if you need further information please ask.

This is the second stage of the problem is a way to reorganise the recreated data above! This data will have a maximum of 11 cells filled with 3 initials from the possible 18 columns. I then need to arrange the data so that the last 7 columns are deleted but the first 1 are filled with the data (skip blanks - which in excel does not do what it sounds like).

RE: Basic offset and 'skip blanks'

I have managed to correct the first stage of code

Sub NextCopyDataOver()
'From now on it is reorganise of initials

Sheets("organise").Select
Range("ak4").Select
Range("AK4:BC4").Formula = "=IF(RC[-20]=1,R3C[-20],"""")"
Range(ActiveCell, ActiveCell.Offset(0, 18)).Copy

Do Until ActiveCell.Offset(1, -3) = ""
ActiveCell.Offset(1, 0).Select
ActiveSheet.Paste

Loop

End Sub

Now onto stage 2 - skipping blanks!

 

Training courses

Training information:

Welcome. Please choose your application (eg. Excel) and then post your question.

Our Microsoft Qualified trainers will then respond within 24 hours (working days).

Frequently Asked Questions
What does 'Resolved' mean?

Any suggestions, questions or comments? Please post in the Improve the forum thread.

Excel tip:

Removing the Ribbon from view in Excel 2010

At times when you want to view the whole spreadsheet, try double clicking on the ''Home'' tab on the ribbon which will hide the ribbon from view.

View all Excel hints and tips

Connect with us:

0207 987 3777

Call for assistance

Request Callback

We will call you back

Server loaded in 0.1 secs.