Search : in
By :

Copy a range if the a specific column is blan

Last answer on Feb 3, 2009 2:03:35 am GMT Q, on Jan 22, 2009 3:22:20 pm GMT 
 Report this message to moderators

Hello,

I'm trying to copy a row of data from column A - G if column D from row 8 - 209 is blank to the bottom of my spreadsheet.

I used the following coding, but it's copying the whole row instead of up to column G.

Sub Unposted()

Dim LSearchRow As Integer
Dim LCopyToRow As Integer

On Error GoTo Err_Execute

'Start search in row 8
LSearchRow = 8

'Start copying data to row 170 in WSO - 480251444 (Op) (row counter variable)
LCopyToRow = 223

While Len(Range("A" & CStr(LSearchRow)).Value) > 0

'If value in column D = "", copy entire row to WSO - 480251444 (Op)
If Range("D" & CStr(LSearchRow)).Value = "" Then

'Select row in WSO - 480251444 (Op) to copy
Rows(CStr(LSearchRow) & ":" & CStr(LSearchRow)).Select
Selection.Copy

'Paste row into WSO - 480251444 (Op) in next row
Sheets("WSO - 480251444 (Op)").Select
Rows(CStr(LCopyToRow) & ":" & CStr(LCopyToRow)).Select
ActiveSheet.Paste

'Move counter to next row
LCopyToRow = LCopyToRow + 1

'Go back to Sheet1 to continue searching
Sheets("WSO - 480251444 (Op)").Select

End If

LSearchRow = LSearchRow + 1

Wend

'Position on cell A4
Application.CutCopyMode = False
Range("A4").Select

MsgBox "All matching data has been copied."

Exit Sub

Err_Execute:
MsgBox "An error occurred."

End Sub



Please help!!

Configuration: Windows XP
Internet Explorer 7.0

Best answers for « Copy a range if the a specific column is blan » in :
You can legally download software that is no longer for sale ShowYou can legally download software that is no longer for sale Myth Reality Explanations Myth We have the right to download and copy software if it is no longer for sale. (Or music, film etc) Reality FALSE Explanations It must be...
Excel – Comparing cell A1 to entire A column in Sheet 2 ShowExcel – Comparing cell A1 to entire A column in Sheet 2 Issue Solution Note Issue I have been trying to compare sheet1 A2 to sheet2 A2 through A500 and if it exists somewhere in sheet2's a col then copy that entire row to a new sheet....
[VBA] Deleting a word in a range of cell Show[VBA] Deleting a word in a range of cell In the case you want to delete a word in a sentence, just create a small macro that removes the word. But it will become difficult when you have word like, for example, "Theword" or "THEWORD" or...
Download Shadow Copy ShowNormally, the administrators of files as the explorer Windows do not allow the copy of opened or blocked files. For instance if you are treating a document Word, you have no possibility of copying it in another file during its edition. Shadow Copy is...
Spreadsheet - Formulas ShowIntroduction to Formulas The main use of a spreadsheet is to automate calculations, which means using cells to perform operations based on values in other cells. The spreadsheet recalculates all the values each time a change is made to the value of...
Worksheet - Cells ShowThe Concept of a Cell A "cell" is the intersection between a line (horizontal) and a column (vertical) on a worksheet. Thus, the name of the line combined with the name of the column gives the cell's coordinates (the term address is sometimes also...
Ask your question Reply

1

Tweedledum, on Jan 22, 2009 4:23:39 pm GMT

I dont actually get what you want, do you want to search til you find a blank row? if so its would be something like this

With ActiveSheet
LRow = .Cells(.Rows.Count, "G").End(xlUp).Row

that will count through the rows, sorry i dont have much time and i amn't a vba master but the XLup idea might get you on the right road, if you google it you will find others for going left right up down etc if i have time i might read through your post a but slower and try to see if there is an easy solution

   
Reply to Tweedledum

2

Q, on Jan 22, 2009 7:26:21 pm GMT

Hey!

Thanks for the help, but I'm really new at this so I'm not even sure where that code u replied with goes.

Just to clarify, I have a spreadsheet that I update daily that has data starting at A8 through column M. I want to look in column D for blank spaces if column A has an integer in it. If it does, I want to copy that row of data from column A to G to the bottom of the spreadsheet.

This coding does that, but instead of copy just columns A to G it's copying the entire row. This is causing the data in column H to M to disappear.

Does that help?

---again, thanks for the help!

   
Reply to Q

3

Tweedledum, on Jan 23, 2009 9:56:19 am GMT

To be honest i know very little about vba i have only done one or two projects in it, i would suggest signing up to technet forums because they have a vba forum with people who are actually experts. But i think you might have to define a range to get it to just take a few columns. It would be something like

Dim rDest As Range ' creates range variable
Set rDest = Range("G" & cnt) 'sets range variable

When i set rDest to the range cnt is a counter which updates in a loop so it isnt helpful but if you look for defining ranges on google you might find a way to set the range between two column values. Sorry i cant be of more help but its a avenue of research that might help you.

   
Reply to Tweedledum

4

 Helper, on Feb 3, 2009 2:03:35 am GMT

Assumptions:
1. Column H does not have any blank cells so it will be used to stop the loop.

I tried this with test data based on your description.

Private Sub CommandButton1_Click()

Dim r
r = Range("A65536").End(xlUp).Row
Dim i
Dim j
j = 8

Do While Not IsEmpty(Range("H" & j))

If IsNumeric(Range("A" & j)) And Range("D" & j) = "" Then
r = r + 1
Range("A" & r) = Range("A" & j)
Range("B" & r) = Range("B" & j)
Range("C" & r) = Range("C" & j)
Range("D" & r) = Range("D" & j)
Range("E" & r) = Range("E" & j)
Range("F" & r) = Range("F" & j)
Range("G" & r) = Range("G" & j)
End If

j = j + 1
Loop

End Sub

   
Reply to Helper
Ask your question Reply
They need your help